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

Postgresql – Unnest objects from a jsonb array into separate rows

I have a table in Postgres 14.9:

Name (Txt) Detail (JSONB) state (JSONB)
apple [{ "code": "156", "color": "red" }, { "code": "156", "color": "blue" }] [{ "ap": "good", "op2": "bad" }]
orange [{ "code": "156", "color": "red" }, { "code": "235", "color": "blue" }] [{ "op": "bad", "op2": "best" }]
lemon [{ "code": "156", "color": "red" }, { "code": "156", "color": "blue" }] [{ "cp": "best", "op2": "good" }]

I want this select sql output :

Name (Txt) Detail (JSONB) state (JSONB)
apple { "code": "156", "color": "red"} { "ap": "good", "op2": "bad" }
apple { "code": "156", "color": "blue"} { "ap": "good", "op2": "bad" }
orange { "code": "156", "color": "red" } { "op": "bad", "op2": "best" }
lemon { "code": "156", "color": "red" } { "cp": "best", "op2": "good" }
lemon { "code": "156", "color": "blue"} { "cp": "best", "op2": "good" }

My attempt:

SELECT
    "Name (Txt)"
 ,  jsonb_build_object('code', elem->>'code', 'color', elem->>'color')  AS "Detail (JSONB)"
 ,  state::JSONB "
FROM your_table,
     jsonb_array_elements("Detail (JSONB)") AS elem,
     state::JSONB
WHERE elem->>'code' = '156';
Tags:

3

Answers


  1. 0

    Welcome to stackoverflow

    Considering yellow in your output is a typo.

    I have a table with the name test_table. Schema of the table is

    name (text)
detail (jsonb)
state (jsonb)

    Following query will give the desired output.

    select                              
  table_1.name, 
  table_1.details, 
  table_2.state 
from 
  (
    select 
      name, 
      jsonb_array_elements(detail) as details 
    from 
      test_table
  ) table_1 
  join (
    select 
      name, 
      jsonb_array_elements(state) as state 
    from 
      test_table
  ) table_2 on table_1.name = table_2.name 
where 
  table_1.details ->> 'code' = '156';
    Login or Signup to reply.
  2. 0

    You could unnest your array with JSONB_TO_RECORDSET, then remap your json objects with JSONB_BUILD_OBJECT.

    SELECT tab.name, 
       JSONB_BUILD_OBJECT('code', cte.code, 'color', cte.color) AS "Detail (JSONB)",
       tab.state AS "State (JSONB)" 
FROM       tab
CROSS JOIN JSONB_TO_RECORDSET(tab.Detail) cte(  
               code  VARCHAR(30),
               color VARCHAR(30)
           )

    "Output":

    name Detail (JSONB) State (JSONB)
    apple {"code":"156","color":"red"} [{"ap":"good","op2":"bad"}]
    apple {"code":"156","color":"blue"} [{"ap":"good","op2":"bad"}]
    orange {"code":"156","color":"yellow"} [{"op":"bad","op2":"best"}]
    lemon {"code":"156","color":"red"} [{"cp":"best","op2":"good"}]
    lemon {"code":"156","color":"blue"} [{"cp":"best","op2":"good"}]

    Check the demo here.

    Login or Signup to reply.
  3. 0

    jsonb_array_elements() does exactly what you are asking for, in a single step:

    SELECT name, jsonb_array_elements(detail) AS detail, state
FROM   tbl;

    fiddle

    See:

    If there can be empty arrays or null in detail, here is a variant to preserve all input rows:

    SELECT t.name, e.detail, t.state
FROM   tbl t
LEFT   JOIN LATERAL jsonb_array_elements(t.detail) AS e(detail) ON true;

    See:

    Of course, detail must be a jsonb array, or you get an error. You might test with jsonb_typeof() first:

    See:

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

Back To Top
Search