Clean way to query complex JSON in Postgresql

Question

I have JSON data stored in a JSONB field in my postgresql 9.5 DB.

Is there a way of making sub-objects columns without knowing which column is a sub-object?

JSON example in question:

{
   "a":1,
   "b":[1,2,3],
   "c":"bar",
   "d":{
      "key1":"value1",
      "key2":"value2"
   }
 }

I can use the following to get all of the keys from a JSON object.

SELECT * FROM json_object_keys('{"a":1,"b":[1,2,3],"c":"bar", "d":{"key1":"value1", "key2":"value2"}}')

At that point I can then use json_to_record() but I would like to split the column out to their own separate fields.

select * from json_to_record('{"a":1,"b":[1,2,3],"c":"bar", "d":{"key1":"value1", "key2":"value2"}}') as x(a int, b text, c text, d text)

gets me

a| b       | c   | d     
1| [1,2,3] | bar | {"key1":"value1", "key2":"value2"}

Is there a way to get something like this back, preferably in a single query?

--------------------------------------------------------------------
a| b       | c   | d                                  | key1  | key2     
1| [1,2,3] | bar | {"key1":"value1", "key2":"value2"} |value1 |value2

Dzmitry Savinkou · Accepted Answer · 2016-04-20 16:17:42Z

5

WITH t(v) AS ( VALUES
  ('{
     "a":1,
     "b":[1,2,3],
     "c":"bar",
     "d":{
        "key1":"value1",
        "key2":"value2"
     }
   }'::JSONB)
)
SELECT x1.*,x2.* FROM t,
    jsonb_to_record(v) as x1(a int,b text,c text,d jsonb),
    jsonb_to_record(v->'d') as x2(key1 text,key2 text);

Result:

 a |     b     |  c  |                  d                   |  key1  |  key2  
---+-----------+-----+--------------------------------------+--------+--------
 1 | [1, 2, 3] | bar | {"key1": "value1", "key2": "value2"} | value1 | value2
(1 row)

answered Apr 20, 2016 at 16:17

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

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

1 Comment

Kevin Vasko Over a year ago

Thanks! That gets me 95% there. Is there any way to generate the jsonb_to_record(v->'d') for all objects on that level if you don't know explicitly that D is another JSON object? I found a json_typeof() table function that gets the type. Would it be a bad practice of generating the SQL from another query? The challenge stems from extracting contents of tables adhoc build Microsoft Word documents. We don't know how many columns each table has and we don't know the names of the columns. None of the column header names or number of columns between are consistent from table to table either.

Collectives™ on Stack Overflow

Clean way to query complex JSON in Postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related