unnest row into multiple json objects

Question

Currently I have a table my_table with columns (id,product_id,text) and transform data into a json object like so:

SELECT
  json_agg(
    json_build_object(
      'id', t.id,
      'parent_id', t.product_id,
      'text', t.text
    )
  )
FROM my_table AS t

Now that I am adding more columns to my_table, I need to return a JSON object per each row from the selected list of rows from this table. Basically, talbe columns will be (id,product_id,text1,text2,text3)

And I want to return 3 identical objects with 1 different value of text (for text1,text2,text3)

How can I achieve this?

klin · Accepted Answer · 2019-11-13 19:42:48Z

1

Use unnest() to yield a single row as three ones:

select id, product_id, unnest(array[text1, text2, text3]) as text
from my_table

Create the json array from the above query:

select
    json_agg (
        json_build_object(
            'id', id,
            'product_id', product_id,
            'text', text
        )
    )
from (
    select id, product_id, unnest(array[text1, text2, text3]) as text
    from my_table
    ) t

or

select
    json_agg (
        json_build_object(
            'id', id,
            'product_id', product_id,
            'text', text
        )
    )
from my_table
cross join unnest(array[text1, text2, text3]) as text

edited Nov 13, 2019 at 19:42

answered Nov 13, 2019 at 19:07

klin

123k15 gold badges241 silver badges263 bronze badges

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

7 Comments

Andrey Deineko Over a year ago

Thanks a lot! I was trying to use unnest in my FROM (...) but I did not realize how it worked in fact.

klin Over a year ago

Using the function in the from clause with lateral join perhaps is more elegant, see the updated answer.

Andrey Deineko Over a year ago

To me, the initial answer reads easier. Are there any reasons one should prefer the latter?

klin Over a year ago

They are roughly equivalent in this case. Anyway, I would recommend taming with the second variant as it may be very helpful in more complex queries.

Andrey Deineko Over a year ago

I see. I'll try to!

|

Collectives™ on Stack Overflow

unnest row into multiple json objects

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related