From: | Steve Midgley <science(at)misuse(dot)org> |
---|---|
To: | JP <janis(at)puris(dot)lv> |
Cc: | pgsql-sql <pgsql-sql(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Removing JSONB key across all elements of nested array |
Date: | 2021-10-11 19:09:37 |
Message-ID: | CAJexoSJjC=nnNng0Lrc2pBegbt9k5rZ-eDeATynBh6setfyQrA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Sun, Oct 10, 2021 at 1:45 PM JP <janis(at)puris(dot)lv> wrote:
> 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
>
>
> I took a look at your json and query and I can't figure out what your SQL
select is actually doing. It seems to return the exact same results as a
straight query of your original data?
Here's a sandbox where I put your data and query for examination:
https://www.db-fiddle.com/f/qBhWGyTttT2qqVmw76AJSo/0
Can you please clarify what you're trying to accomplish with the query
(like what output do you want)..
Thanks,
Steve
From | Date | Subject | |
---|---|---|---|
Next Message | Shaozhong SHI | 2021-10-12 16:30:26 | Fault with initcap |
Previous Message | Erik Brandsberg | 2021-10-11 16:59:06 | Re: LISTEN / NOTIFY |