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

Postgresql, JSONB, how to query a joined string from an array of objects

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);

enter image description here


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

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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;
    id objlist
    1 A,B
    2 C,D
    3
    Login or Signup to reply.
  3. 0

    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).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search