From: | JP <janis(at)puris(dot)lv> |
---|---|
To: | pgsql-sql(at)lists(dot)postgresql(dot)org |
Subject: | Removing JSONB key across all elements of nested array |
Date: | 2021-10-10 20:45:08 |
Message-ID: | E1755008-044A-4C24-A18C-AAF04B52ED28@puris.lv |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
I'm trying to remove JSONB key from following sample JSON
{
"spec": {
"id": "485197a6-253a-42b3-9c07-6bac07c02166",
"buildings": [
{
"id": "1b6754b5-c1db-4fdd-af39-32ac173c88cb",
"equipment": {
"selected_inverters": {
"15c9e4a2-5dc7-4017-a09f-1d8e75bdfaef": {
"count": 1
}
}
}
},
{
"id": "0c6d0627-9fd9-4989-819c-35743640052d",
"equipment": {
"selected_inverters": {
"125a2eb4-f26f-4d07-89fa-f14df9dac7cf": {
"count": 2
}
}
}
}
]
}
}
I've succeeded to do so with following query
SELECT
my_jsonb #- '{spec,buildings,0,equipment,selected_inverters}') #- '{spec,buildings,1,equipment,selected_inverters}' AS my_jsob
FROM my_table
This feels like a nasty solution, more so.. I may have various number of dicts in the buildings array.
Does anyone have some ideas on how I could implement something like the following?
SELECT
my_jsonb #- '{spec,buildings,*,equipment,selected_inverters}') AS my_jsob
FROM my_table
From | Date | Subject | |
---|---|---|---|
Next Message | aditya desai | 2021-10-11 07:48:44 | Re: Create trigger on after logon on schema |
Previous Message | Rob Sargent | 2021-10-09 18:29:27 | Re: How to capture error message and save to a table in PostgreSQL? |