I've a JSONB inside a PostgreSQL table with this structure (more or less)

{
    "obj1": {
        "obj2": {
            "obj3": [
                {
                    "obj4": {
                        "obj": "A"
                    }
                },
                {
                    "obj4": {
                        "obj": "B"
                    }
                }
            ]
        }
    }
}

Then my obj3 is an array of objects and I wanna the obj inside obj4 separated by comma.

Thus what I really need is something like:

1 | A,B
2 | C,D
3 | NULL

I'm using PostgreSql 14. Any help is going to be appreciate.

and I've got this

select t.id, 
jsonb_path_query(t.b, '$."obj1"."obj2"."obj3"[*]."obj4"."obj"'::jsonpath) as obj5
from (
    values (1, '{"obj1":{"obj2":{"obj3":[{"obj4":{"obj":"A"}},{"obj4":{"obj":"B"}}]}}}'::jsonb), 
    (2, '{"obj1":{"obj2":{"obj3":[{"obj4":{"obj":"C"}},{"obj4":{"obj":"D"}}]}}}'::jsonb), 
    (3, '{}'::jsonb)
) t(id, b);

But the json_path_query multiply the rows and remove not found results as well...

CodePudding user response：

You need to group the resulting rows by t.id so that to group A & B, and C & D on the same row while using the string_agg function to group them in the same resulting column with ',' as separator.

But to do so, you need first to switch the jsonb_path_query function from the SELECT clause to the FROM clause while introducing a LEFT JOIN so that to keep the rows with no output from the jsonb_path_query function.

The solution is :

select t.id, string_agg(obj5->>'obj', ',') AS result
from (
    values (1, '{"obj1":{"obj2":{"obj3":[{"obj4":{"obj":"A"}},{"obj4":{"obj":"B"}}]}}}'::jsonb), 
    (2, '{"obj1":{"obj2":{"obj3":[{"obj4":{"obj":"C"}},{"obj4":{"obj":"D"}}]}}}'::jsonb), 
    (3, '{}'::jsonb)
) t(id, b)
left join lateral jsonb_path_query(t.b, '$.obj1.obj2.obj3[*].obj4') as obj5
  on TRUE
group by t.id;

see dbfiddle

CodePudding user response：

Inside-out: climb the object tree, flatten the array and then select/aggregate. DB fiddle

select id, (
    select string_agg(j->'obj4'->>'obj', ',')
    from jsonb_array_elements(b->'obj1'->'obj2'->'obj3') as j
  ) as objlist
from the_table;

CodePudding user response：

For the sake of clarity/reuse, I'd create a function to convert the jsonb array to a Postgres array.

CREATE OR REPLACE FUNCTION jsonb_text_array(jsonb)
                   RETURNS text[]
                  LANGUAGE sql
                  PARALLEL SAFE
                 LEAKPROOF
                    STRICT
                        AS $$
  SELECT array_agg(t)
    FROM jsonb_array_elements_text($1) x(t)
  ;
$$;

Then this query should return what you want.

SELECT t.id
     , jsonb_text_array(jsonb_path_query_array(t.b, '$.obj1.obj2.obj3.obj4.obj')) AS obj5
  FROM ( VALUES
         (1, '{"obj1":{"obj2":{"obj3":[{"obj4":{"obj":"A"}},{"obj4":{"obj":"B"}}]}}}'::jsonb)
       , (2, '{"obj1":{"obj2":{"obj3":[{"obj4":{"obj":"C"}},{"obj4":{"obj":"D"}}]}}}'::jsonb)
       , (3, '{}'::jsonb)
       ) t(id, b);

If you really want a string returned instead of an array, change the function to return text instead of text[] and use string_agg(t, ',') instead of array_agg(t).