Parse JSON string down to key and values in Postgres

Question

I'm trying to parse a JSON string into a table with it's value pairs.

Here is the JSON string:

{
"route": {
    "coding": [
        {
            "code": "56",
            "display": "ORAL",
            "userSelected": true
        }
    ]
}
}

My goal is to get it into a table like such:

| parent | key          | value                 | type    |
-------------------------------
| null   | route        | {coding: [...]}       | object  |
| route  | coding       | [{"code": "56", ...}] | array   |
| route  | coding       | {"code": "56", ...}   | object  |
| coding | code         | 56                    | integer |
| coding | display      | ORAL                  | text    |
| coding | userselected | true                  | boolean |

I'm struggling with making a recursive call. I am able to either parse the array or the object, I just cannot figure out how to call one or the other based upon the type.

This is my current code:

WITH RECURSIVE temp (parent, key, value, type) AS (
SELECT parent, key, value, type
FROM t1 
UNION ALL
SELECT parent, key, value, jsonb_typeof(value) AS type
FROM (
SELECT key AS parent, (jsonb_each(value)).*
FROM temp
WHERE temp.type = 'object') AS p1              
), temp2 (parent, key, value, type) AS (
SELECT parent, key, value, type
FROM t1 
UNION ALL
SELECT parent, key, jsonb_array_elements(value), 'object' AS type
FROM temp2
WHERE temp2.type = 'array'
)
SELECT parent, key, value, type FROM temp;

Any help is greatly appreciated. Thanks.

There is no "parent" or "key" or "value" in your sample JSON. Please edit your question and show us the expected output based on your sample JSON — user330315
– user330315, Commented Sep 24, 2018 at 19:56

klin · Accepted Answer · 2018-09-24 22:26:44Z

In this answer you can find a query which extracts paths from a jsonb object. It can be easily modified to get your expected output:

with recursive extract_all as
(
    select
        null as parent,
        key, 
        value,
        jsonb_typeof(value) as type
    from my_table
    cross join lateral jsonb_each(jdata)
union all
    select
        key,
        coalesce(obj_key, (arr_key- 1)::text),
        coalesce(obj_value, arr_value),
        jsonb_typeof(coalesce(obj_value, arr_value))
    from extract_all
    left join lateral 
        jsonb_each(case jsonb_typeof(value) when 'object' then value end) 
        as o(obj_key, obj_value) 
        on jsonb_typeof(value) = 'object'
    left join lateral 
        jsonb_array_elements(case jsonb_typeof(value) when 'array' then value end) 
        with ordinality as a(arr_value, arr_key)
        on jsonb_typeof(value) = 'array'
    where obj_key is not null or arr_key is not null
)
select *
from extract_all;

Result:

 parent |     key      |                                value                                |  type   
--------+--------------+---------------------------------------------------------------------+---------
        | route        | {"coding": [{"code": 56, "display": "ORAL", "userSelected": true}]} | object
 route  | coding       | [{"code": 56, "display": "ORAL", "userSelected": true}]             | array
 coding | 0            | {"code": 56, "display": "ORAL", "userSelected": true}               | object
 0      | code         | 56                                                                  | number
 0      | display      | "ORAL"                                                              | string
 0      | userSelected | true                                                                | boolean
(6 rows)

Note that array elements have no keys, so we should use their indexes to identify them. Formally, coding is not a parent of nested array elements.

Working example in rextester.

Collectives™ on Stack Overflow

Parse JSON string down to key and values in Postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related