Get full path of every nested json value

Question

I need that generate full path's to all nested json values. Level of childs are unlimited.

Create sample data:

drop table if exists test_nested;
create table test_nested (my_jsonb jsonb);
insert into test_nested values(
'{
"name": "Jon",
"last_name": "Smith",
"address": {
    "country": "USA",
    "city": "Miami"
},
"legal_address": {
    "country": "Brazil",
    "city": "Rio"
}
}');

I can get all nested keys and values, but where I stuck is, how to get full paths of all those keys ?

This is what I have:

 with recursive get_keys (k, obj, val) as (
    select t.k,  case jsonb_typeof(test_nested.my_jsonb -> t.k)  when 'object' then test_nested.my_jsonb -> t.k else null end, -- just get it's nested or not
    test_nested.my_jsonb->t.k
    from test_nested  
    cross join jsonb_object_keys(test_nested.my_jsonb) as t(k)
    union all
    select t.k, case jsonb_typeof(get_keys.obj -> t.k)  when 'object' then get_keys.obj -> t.k else null end,
    get_keys.obj->t.k
    from get_keys  
    cross join jsonb_object_keys(get_keys.obj) as t(k)
)
select k, val, obj
from get_keys;

it gives:

k             | val
---------------------------
name          | Jon
last_name     | Smith
address       | json_object_here
country       | USA
city          | Miami
legal_address | json_object_here
country       | Brazil
city          | Rio

What I need:

k             | val                | full_path
---------------------------------------------------
name          | Jon                | /
last_name     | Smith              | /
address       | json_object_here   | /
country       | USA                | address/
city          | Miami              | address/
legal_address | json_object_here   | /
country       | Brazil             | legal_address/
city          | Rio                | legal_address/

Any help greatly appreciated

I think this answer is close to what you need: https://stackoverflow.com/questions/45585462/recursively-flatten-a-nested-jsonb-in-postgres-without-unknown-depth-and-unknown/45592457#45592457 — Bjarni Ragnarsson, Mar 03 '20 at 08:23

score 0 · Answer 1 · answered Jun 21 '21 at 03:52

This allows for arbitrary depth, accommodates both objects and arrays nested at any level. The intent of this code is for graph traversal expressed as JSON, so it's aggregating the various paths found for any given object key and/or array value. It currently ignores array indexes as part of the discovered path, but the first CTE does capture them, so the other two CTEs could be modified to include the array indexes as part of the path if desired.

WITH RECURSIVE
    objects_flat AS (
        SELECT
              obj.my_jsonb                            AS obj_old
            , CASE jsonb_typeof(obj.my_jsonb)
                WHEN 'array' THEN
                    CASE jsonb_typeof(new_obj.info -> 'value')
                        WHEN 'array' THEN
                            NULL::text
                        WHEN 'object' THEN
                            NULL::text
                        ELSE
                            (new_obj.info -> 'value')::text
                    END
                WHEN 'object' THEN
                    REPLACE((new_obj.info -> 'key')::text, '"', '')
                ELSE
                    NULL::text
              END                                       AS "key"
            , CASE jsonb_typeof(obj.my_jsonb)
                WHEN 'array' THEN
                    (new_obj.info -> 'index')::int
                WHEN 'object' THEN
                    NULL::int
                ELSE
                    NULL::int
              END                                       AS "index"
            , CASE jsonb_typeof(obj.my_jsonb)
                WHEN 'array' THEN
                    (obj.my_jsonb -> (new_obj.info -> 'index')::int)
                WHEN 'object' THEN
                    obj.my_jsonb -> REPLACE((new_obj.info -> 'key')::text, '"', '')
                ELSE
                    NULL::jsonb
              END                                       AS obj_new
            , CASE jsonb_typeof(obj.my_jsonb)
                WHEN 'array' THEN
                    '{}'::text[]
                WHEN 'object' THEN
                    ARRAY[REPLACE((new_obj.info -> 'key')::text, '"', '')]     -- ARRAY concat
                ELSE
                    NULL::text[]
              END                                       AS "location"
            , CASE jsonb_typeof(
                CASE jsonb_typeof(obj.my_jsonb)
                    WHEN 'array' THEN
                        (obj.my_jsonb -> (new_obj.info -> 'index')::int)
                    WHEN 'object' THEN
                        obj.my_jsonb -> REPLACE((new_obj.info -> 'key')::text, '"', '')
                    ELSE
                        NULL::jsonb
                END
              )
                WHEN 'array' THEN
                    FALSE
                WHEN 'object' THEN
                    FALSE
                ELSE
                    TRUE
              END                                       AS is_leaf
            , 0                                         AS pass
        FROM
                test_nested obj
              , LATERAL (
                    SELECT
                        unnest(
                            CASE jsonb_typeof(obj.my_jsonb)
                                WHEN 'array' THEN
                                    ARRAY(
                                        SELECT
                                            jsonb_build_object(
                                                  'type', 'array'
                                                , 'index', ae."index"
                                                , 'key', NULL
                                                , 'value', ae.value
                                            )
                                        FROM
                                            (
                                                SELECT
                                                      ROW_NUMBER() OVER (ORDER BY 1) - 1 AS "index"
                                                    , ae.value
                                                FROM
                                                    jsonb_array_elements(obj.my_jsonb) AS ae
                                            ) ae
                                    )
                                WHEN 'object' THEN
                                    ARRAY(
                                        SELECT
                                            jsonb_build_object(
                                                  'type', 'object'
                                                , 'index', null
                                                , 'key', kv."key"
                                                , 'value', kv.value
                                            )
                                        FROM
                                            (
                                                SELECT
                                                      ROW_NUMBER() OVER (ORDER BY 1) - 1 AS "index"
                                                    , kv."key"
                                                    , kv.value
                                                FROM
                                                    jsonb_each(obj.my_jsonb) AS kv
                                            ) kv
                                    )
                            END
                        ) AS info
              ) new_obj
        UNION
        SELECT
              objf.obj_new                                AS obj_old
            , CASE jsonb_typeof(objf.obj_new)
                WHEN 'array' THEN
                    CASE jsonb_typeof(new_obj.info -> 'value')
                        WHEN 'array' THEN
                            NULL::text
                        WHEN 'object' THEN
                            NULL::text
                        ELSE
                            (new_obj.info -> 'value')::text
                    END
                WHEN 'object' THEN
                    REPLACE((new_obj.info -> 'key')::text, '"', '')
                ELSE
                    NULL::text
              END                                       AS "key"
            , CASE jsonb_typeof(objf.obj_new)
                WHEN 'array' THEN
                    (new_obj.info -> 'index')::int
                WHEN 'object' THEN
                    NULL::int
                ELSE
                    NULL::int
              END                                       AS "index"
            , CASE jsonb_typeof(objf.obj_new)
                WHEN 'array' THEN
                    (objf.obj_new -> (new_obj.info -> 'index')::int)
                WHEN 'object' THEN
                    objf.obj_new -> REPLACE((new_obj.info -> 'key')::text, '"', '')
                ELSE
                    NULL::jsonb
              END                                       AS obj_new
            , CASE jsonb_typeof(objf.obj_new)
                WHEN 'array' THEN
                    objf."location"
                WHEN 'object' THEN
                    objf."location" || REPLACE((new_obj.info -> 'key')::text, '"', '')     -- ARRAY concat
                ELSE
                    null
              END                                       AS "location"
            , CASE jsonb_typeof(
                CASE jsonb_typeof(objf.obj_new)
                    WHEN 'array' THEN
                        (objf.obj_new -> (new_obj.info -> 'index')::int)
                    WHEN 'object' THEN
                        objf.obj_new -> REPLACE((new_obj.info -> 'key')::text, '"', '')
                    ELSE
                        NULL::jsonb
                END
              )
                WHEN 'array' THEN
                    FALSE
                WHEN 'object' THEN
                    FALSE
                ELSE
                    TRUE
              END                                       AS is_leaf
            , objf.pass + 1                               AS new_obj_info
        FROM
              objects_flat objf
            , LATERAL (
                SELECT
                    unnest(
                        CASE jsonb_typeof(objf.obj_new)
                            WHEN 'array' THEN
                                ARRAY(
                                    SELECT
                                        jsonb_build_object(
                                              'type', 'array'
                                            , 'index', ae."index"
                                            , 'key', NULL
                                            , 'value', ae.value
                                        )
                                    FROM
                                        (
                                            SELECT
                                                  ROW_NUMBER() OVER (ORDER BY 1) - 1 AS "index"
                                                , ae.value
                                            FROM
                                                jsonb_array_elements(objf.obj_new) AS ae
                                        ) ae
                                )
                            WHEN 'object' THEN
                                ARRAY(
                                    SELECT
                                        jsonb_build_object(
                                              'type', 'object'
                                            , 'index', null
                                            , 'key', kv."key"
                                            , 'value', kv.value
                                        )
                                    FROM
                                        (
                                            SELECT
                                                  ROW_NUMBER() OVER (ORDER BY 1) - 1 AS "index"
                                                , kv."key"
                                                , kv.value
                                            FROM
                                                jsonb_each(objf.obj_new) AS kv
                                        ) kv
                                )
                        END
                    ) AS info
              ) new_obj
        WHERE
                objf.is_leaf = FALSE
      )
    , paths AS (
        SELECT
              objf."key"::int                         AS "key"
            , array_remove(objf."location", objf."key") AS valid_path
        FROM
            objects_flat objf
        WHERE
            objf."key" IS NOT NULL
        UNION
        SELECT
              objf.obj_new::int                       AS "key"
            , objf."location"                         AS valid_path
        FROM
            objects_flat objf
        WHERE
            objf.is_leaf = TRUE
      )
    , valid_paths AS (
        SELECT
              vp."key"
            , jsonb_agg(vp.valid_path) AS valid_paths
        FROM
            paths vp
        GROUP BY
            vp."key"
      )
SELECT DISTINCT
      p.valid_path[array_upper(p.valid_path, 1)]::int AS key_parent
    , p."key"                                      AS key_child
    , vp.valid_paths
FROM
    paths p
    INNER JOIN valid_paths vp
        ON vp."key" = p."key"

Get full path of every nested json value

1 Answers1