0

I have the following table:

"Id"|                  "Data"====+================================================1   | { "emp": [     |            {"id": "a1", "otherdata": "other"},     |            {"id": "a2", "otherdata": "other"}     |          ]    | }----+------------------------------------------------2   | { "emp": [     |            {"id": "b1", "otherdata": "other"},     |            {"id": "b2", "otherdata": "other"}     |          ]    | }-----------------------------------------------------

Where "Data" is jsonb.

I need to create a temporary table of this type:

"Id"| "Emp"====+=============1   | {"a1", "a2"}----+-------------2   | {"b1", "b2"}

How can I do this?

askedNov 26, 2018 at 9:06
Vas Mil's user avatar

1 Answer1

1

Usejsonb_to_recordset to extract the array values to rows, group them, then back to an array usingarray_to_json.

SELECT a.id, array_to_json(array_agg(b.id)) AS empFROM mytable aCROSS JOIN jsonb_to_recordset(a.data->'emp') AS b(id text)GROUP BY a.id;

SeeSQL Fiddle

answeredNov 26, 2018 at 9:43
Andy N's user avatar
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

Sign up orlog in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

By clicking “Post Your Answer”, you agree to ourterms of service and acknowledge you have read ourprivacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.