Unnest objects from a jsonb array into separate rows

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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:

• Query for array elements inside JSON type

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:

• What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?

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

See:

• Conditional update with jsonb_set()