jsonb handle, PostgreSQL

jsonb handle

create table "complains"(complain_id INT  primary key, ccmd jsonb);
insert into "complains" 
  (complain_id, ccmd)
values 
  (1, '[
    {
        "cm_id": 108,
        "cmd_id": 46304,
        "cm_name": "category",
        "cmd_name": "Furniture",
        "installation_date": "null",
        "warranty_duration": 0
    },
    {
        "cm_id": 109,
        "cmd_id": 0,
        "cm_name": "brand",
        "cmd_name": "",
        "installation_date": "null",
        "warranty_duration": 0
    },
    {
        "cm_id": 110,
        "cmd_id": 0,
        "cm_name": "model",
        "cmd_name": "",
        "installation_date": "null",
        "warranty_duration": 0
    }
]');
insert into "complains" 
  (complain_id, ccmd)
values 
  (2, '{}');

create table asset_tat(tat_id INT primary key, cmd_id INT);
insert into asset_tat (tat_id, cmd_id)values
(1, 46304);
              
select o.complain_id, so.*
from "complains" o
  cross join lateral jsonb_array_elements(case jsonb_typeof(ccmd) when 'array' then ccmd else '[]' end) j(suborder11)
  join asset_tat so on so.cmd_id ::TEXT = j.suborder11 ->> 'cmd_id'


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development