N
N
Neuro2019-10-02 10:54:18
PostgreSQL
Neuro, 2019-10-02 10:54:18

How to get an array with many objects from a sql query?

As a result of the sql query, I want to get an object with a test field that will be an array and will contain various objects.

SELECT  sensor_instances.*,
    row_to_json((SELECT d
      FROM  (SELECT groups.*) d
    )) AS group,
    row_to_json((SELECT a
      FROM (SELECT addresses.*) a
    )) AS currentAddress,
    ARRAY(
      SELECT sensor_parameters.*
      FROM sensor_parameters
      WHERE sensor_parameters.sensortype IN (sensor_instances.sensortype)
    ) AS test
FROM sensor_instances
LEFT JOIN groups ON groups.id = sensor_instances.groupid 
LEFT JOIN addresses ON sensor_instances.addressid = addresses.id
WHERE sensor_instances.id IN (89)

But as a result, the request returns an error
Query 1 ERROR: ERROR:  subquery must return only one column
LINE 8:   ARRAY(

Maybe someone knows how to get an array with a list of objects in the test field?
you need to get the following structure:
{
  test: [
  {},
  {}
 ]
}

Answer the question

In order to leave comments, you need to log in

1 answer(s)
S
Sergey Tikhonov, 2019-10-02
@tumbler

ARRAY(
      SELECT sensor_parameters.*
      FROM sensor_parameters
 ...

Your array is multidimensional, postgres does not expect this. Leave 1 field inside

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question