Postgres SQL : rows to name, value json pairs

Question

Given a row with Col1, Col2. How to convert the row into

[
    {
        "name": "Col1",
        "value": "Column1Value"
    },
    {
        "name": "Col2",
        "value": "Column2Value"
    }
]
``

user330315 · Accepted Answer · 2021-08-19 14:46:43Z

2

You can use a scalar subselect:

select (select jsonb_agg(jsonb_build_object('name', key, 'value', value))
        from jsonb_each(to_jsonb(d)) as j)
from the_table d

answered Aug 19, 2021 at 14:46

user330315

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

Comments

Stefanov.sm · Accepted Answer · 2021-08-19 14:51:16Z

1

Here it is. Substitute the_table with your actual table name.

with jsonrows(jsonobject, rownumber) as 
(
    select to_json(t), row_number() over () 
    from the_table t
)
select json_agg(json_build_object('name', key, 'value', value))
from jsonrows 
cross join lateral json_each_text(jsonobject) 
group by rownumber;

edited Aug 19, 2021 at 14:51

answered Aug 19, 2021 at 14:44

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

1 Comment

Stefanov.sm Over a year ago

The solution of @a_horse_with_no_name is better I think ...

Collectives™ on Stack Overflow

Postgres SQL : rows to name, value json pairs

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related