How to return ARRAY of multiple fields from subquery

Question

I need top level query that returns array of subquery that returns multiple fields. Simple select array(select ...); does not work due to following error:

subquery must return only one column

Part of query:

(SELECT array(SELECT
                logs_log.message -> 'slot_date',
                logs_log.message -> 'start_time',
                logs_log.message -> 'end_time',
                logs_log.message -> 'source',
                logs_log.message -> 'phone_number'
              FROM
                logs_log
              WHERE
                logs_log.message ->> 'phone_number' = leads_lead.phone_number))
  AS previous_appointments

Any ideas how can I achieve result I described above?

logs_log table:

id, object_id, action_time, content_type_id, user_id, message
28138, 0d36a20d-a251-41c3-ba7e-2d270004f4b1, 2017-01-27 06:48:06.550265+00, 18, 1, {"source": "driver_email", "end_time": "13:30:00", "slot_date": "0333-03-23", "start_time": "13:00:00", "phone_number": "1231231231"}
28137, 0d074daa-0c77-4f96-b512-248cdfb9263b, 2017-01-24 21:31:16.140453+00, 18, 1, {"source": "driver_email", "end_time": "13:30:00", "slot_date": "3333-03-23", "start_time": "13:00:00", "phone_number": "9111111992"}

leads_lead table:

id, first_name, second_name, phone_number, city_id, source_id, external_id, attributes, created_at, ip_address, partner_id, tags
ab99dbba-d339-407a-8e2d-c0ffd676213b,Денис,Антонов,7123123123,b3b62c53-6815-4898-b3b6-23a1196c1986,7ee38a21-32d0-4362-8519-0bc6e6ce37b8,20391438-4d19-48b3-aae0-8d1ab0d7ef93,"""{\""uuid\"":\""20392223-4d19-48b3-aae0-8d1ab0d7ef93\""}""",2016-11-07 17:30:00.583425+00,,,

Please Edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots — user330315
– user330315, Commented Jan 27, 2017 at 8:30
Right. Do you want to return an array of jsonb objects per leads_lead row or multiple result rows per leads_lead row, each containing the jsonb attributes as array? — Laurenz Albe
– Laurenz Albe, Commented Jan 27, 2017 at 9:47
@LaurenzAlbe The resulting previous_appointments column should look like [["22-01-2017", "12:00:00", "13:00:00", "email", "23123123"]] — Dmitrijs Zubriks
– Dmitrijs Zubriks, Commented Jan 27, 2017 at 10:22
Yes, but what should the result be if there are several logs_log entries for one leads_table row? That's what the error message complains about. — Laurenz Albe
– Laurenz Albe, Commented Jan 27, 2017 at 10:30
@LaurenzAlbe each selected from leads_lead row should contain all related logs_log rows (which is basically multidimensional array). And I think that the error states that subquery should return only 1 column (but it returns multiple). — Dmitrijs Zubriks
– Dmitrijs Zubriks, Commented Jan 27, 2017 at 10:41

Laurenz Albe · Accepted Answer · 2017-01-27 11:32:07Z

2

Change your subselect to

(SELECT array_agg(
           array[
              logs_log.message -> 'slot_date',
              logs_log.message -> 'start_time',
              logs_log.message -> 'end_time',
              logs_log.message -> 'source',
              logs_log.message -> 'phone_number'
           ]
        )
 FROM logs_log
 WHERE logs_log.message ->> 'phone_number' = leads_lead.phone_number
) AS previous_appointments

answered Jan 27, 2017 at 11:32

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to return ARRAY of multiple fields from subquery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related