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

Answer 1

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;

Answer 2

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);