Postgresql, json, query

Question

I want the syntax to query in PostgreSQL for the data in JSON format of the following form into tabular format.

data(JSON):

1. { "attr1":"v1", "attr2":[{"subattr":"a1","subattr2":"aa1"},{"subattr":"a2","subattr2":"aa2"}],"attr3":"vv1"}

2. { "attr1":"v2", "attr2":[{"subattr":"b1","subattr2":"bb1"},{"subattr":"b2","subattr2":"bb2"},{"subattr":"b3","subattr2":"bb3"}],"attr3":"vv2"}

I am stuck with querying the elements in the "attr2".

I want the result in the following form:

attr1	subattr	subattr2	attr3
v1	a1	aa1	vv1
v1	a2	aa2	vv1
v2	b1	bb1	vv2
v2	b2	bb2	vv2
v2	b3	bb3	vv2

My attempt:

select 
    attr2->'subattr' as subattr,
    attr2->'subattr2' as subattr2
from (
    select 
        data->'attr1',
        unnest(array[data->'attr2']) as action'
        data->'attr2'
    from my_table
) as subq

Returning null values

Use [`json_array_elements` or `json_populate_recordset`](https://www.postgresql.org/docs/current/functions-json.html), not `unnest`. If you [edit] your question to include your attempt, I can write an answer that shows how to fix it — Bergi, Aug 28 '23 at 07:22

score 2 · Answer 1 · answered Aug 28 '23 at 07:31

2

You can use json_array_elements function

SELECT
    main_table.json_data->>'attr1' AS attr1,
    subattr->>'subattr' AS subattr,
    subattr->>'subattr2' AS subattr2,
    main_table.json_data->>'attr3' AS attr3
FROM
    your_table AS main_table
LEFT JOIN
    json_array_elements(main_table.json_data->'attr2') AS subattr ON true;

answered Aug 28 '23 at 07:31

Efros Ionelu

226
7

This works on querying for 'attr2', why it might be returning nul vlaues in case of attr1 and attr3 – FR_68 Aug 28 '23 at 07:40
@FR_68 Sure, if your JSON objects don't contain these properties, the `->>` operator returns `NULL`. Please edit your question to show some more example input data with nulls and the expected output – Bergi Aug 28 '23 at 07:50

score 0 · Answer 2 · answered Aug 28 '23 at 07:39

Use json_array_elements instead of unnest, and a lateral table expression instead of a subquery:

SELECT
  data->>'attr1' AS attr1,
  subattr->>'subattr' AS subattr,
  subattr->>'subattr2' AS subattr2,
  data->>'attr3' AS attr3
FROM
  my_table,
  json_array_elements(data->'attr2') AS subattr;

Even more elegant is to use json_to_record(set):

SELECT attr1, subattr, subattr2, attr3
FROM
  my_table,
  json_to_record(my_table.data) AS data(attr1 text, attr2 json, attr3 text),
  json_to_recordset(data.attr2) AS attr2(subattr text, subattr2 text);

Postgresql, json, query

2 Answers2