Question 239438 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql, json, query

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

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search