Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1maNh9-00050T-Ts for pgsql-sql@arkaria.postgresql.org; Tue, 12 Oct 2021 19:44:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1maNh8-0007vg-Gk for pgsql-sql@arkaria.postgresql.org; Tue, 12 Oct 2021 19:44:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1maNh7-0007vW-Pr for pgsql-sql@lists.postgresql.org; Tue, 12 Oct 2021 19:44:14 +0000 Received: from mail-lf1-x12d.google.com ([2a00:1450:4864:20::12d]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1maNh4-0006G6-R4 for pgsql-sql@lists.postgresql.org; Tue, 12 Oct 2021 19:44:13 +0000 Received: by mail-lf1-x12d.google.com with SMTP id y15so1544084lfk.7 for ; Tue, 12 Oct 2021 12:44:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=puris-lv.20210112.gappssmtp.com; s=20210112; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=FiPfM3hXhRhdOGvUb+Maq8LM0mEpEIyoTQHLQ8k8o/U=; b=ppPfJJCFvGxSIiDL5Ci6tQdvN/R8u58Oo4y5hBe/Y7mhYao8RW2h3D62dUpQLE/G3h Eit+v9zy9UzT4Qsg536SFYf/85n12TnHLQKW4g2CEeYV+mXYtnnZIjUL37OwVYnbjuB5 nX6+yj2wCbSwsoH4cYpFEtr7MvoWTCNlnGyQzZb4C5PqFDOhVhPzPUG/df1+Dmt1iInb 0gfryGJrxsO6TmYzwG8ccofGUBxJkC+rMpLiMQLAiKSDwDh3LwKhUoS5pbEByXNgLc+2 g7MmTLR/6g/Tz1EZYCdyLtRHnVTuP2AvinNbjYRLBd7GN5Hlsl2fRCJV+8IFpLwMvkSf rylg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=FiPfM3hXhRhdOGvUb+Maq8LM0mEpEIyoTQHLQ8k8o/U=; b=NqADh1P0QPU/ty+lE9fkKyF2FbHAeamRIxcU/xyBBojd2Tn5ngIhXwJRvtZrIGC5LG MFjGroYwIyJmJwGRFAsVgXD+LbObzFWZO6+F9Z9qIAk9Eo7UrPZ8pEtz/y2ZSe2hXavn EC1xuZFgUqcpr0KOoQG0THIi7SMI2rjbIT2mtjIZ12JXVUEhVn6I/1m4m4Q7AUCMe6x9 HOCKScoS5kApiAK4p0REWOBLp0wIOPKoc9FBf67i8XVYDlc+umeVxin8xDuocdVLZn/o fTau4NXJ7nAW1eD0WOCNhzrs/97GsFuLEztgal+PIClnLaVT3UZ+2OovDPzQbFaikIUg 9wYw== X-Gm-Message-State: AOAM530QxNSR9aup6Z93Ki1NwLit7yGUQBAH238izMLFlMXiBHct/hfw vcZ2OHv6lFZvvW/TMZS63MiIu2yzxkjcIBLgRU4= X-Google-Smtp-Source: ABdhPJyCm9rOSBU7lkb8MPIVaGH/yrZQHs+dfBWmVMSOzjDlh4Elb3Q6Jndd/C+0dFByqiNzvc3CmA== X-Received: by 2002:ac2:4ecf:: with SMTP id p15mr15334030lfr.289.1634067848349; Tue, 12 Oct 2021 12:44:08 -0700 (PDT) Received: from smtpclient.apple (host-37-191-207-235.lynet.no. [37.191.207.235]) by smtp.gmail.com with ESMTPSA id r26sm1112306lfm.237.2021.10.12.12.44.07 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 12 Oct 2021 12:44:07 -0700 (PDT) From: JP Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_584343F8-6FAF-425E-BE89-8B5A9F41756F" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.13\)) Subject: Re: Removing JSONB key across all elements of nested array Date: Tue, 12 Oct 2021 21:44:07 +0200 In-Reply-To: Cc: pgsql-sql To: Steve Midgley References: X-Mailer: Apple Mail (2.3654.120.0.1.13) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_584343F8-6FAF-425E-BE89-8B5A9F41756F Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi Steve, Very strange. It is supposed to remove the element at path given. Clean docker compose with 13.4 PostgreSQL has no problem running exact = same SQL and is successful in removing the key. =E2=9D=AF psql -h 127.0.0.1 -U postgres -d postgres -c 'SELECT = version();' Password for user postgres: version = --------------------------------------------------------------------------= --------------------------------------------------------- PostgreSQL 13.4 (Debian 13.4-1.pgdg110+1) on aarch64-unknown-linux-gnu, = compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit (1 row) Screenshot: https://cln.sh/5PLjiL Running following SQL DROP TABLE IF EXISTS jtest; CREATE TABLE jtest (jfield jsonb); INSERT INTO jtest (jfield) VALUES = ('{"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}}}}]}}'); SELECT 1, jfield #- '{spec,buildings,0,equipment,selected_inverters}' #- = '{spec,buildings,1,equipment,selected_inverters}' FROM jtest UNION SELECT 2, jfield FROM jtest; yields -[ RECORD 1 ]-------- ?column? | 2 ?column? | {"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}}}}]}} -[ RECORD 2 ]-------- ?column? | 1 ?column? | {"spec": {"id": "485197a6-253a-42b3-9c07-6bac07c02166", = "buildings": [{"id": "1b6754b5-c1db-4fdd-af39-32ac173c88cb", = "equipment": {}}, {"id": "0c6d0627-9fd9-4989-819c-35743640052d", = "equipment": {}}]}} But anyhow..=20 What I am trying to achieve is to remove a key nested inside an array, = but from all elements. In the example, I have two buildings in the buildings array, hence I = need to run the #- operation twice, for first and second element. = However the problem is that the array length across the records in table = are variable and am looking to implement something like = '{spec,buildings,*,equipment,selected_inverters}'. BR, JP. > On 11 Oct 2021, at 21:09, Steve Midgley wrote: >=20 >=20 >=20 > On Sun, Oct 10, 2021 at 1:45 PM JP > wrote: > Hi, >=20 > I'm trying to remove JSONB key from following sample JSON >=20 > { > "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 > } > } > } > } > ] > } > } >=20 > I've succeeded to do so with following query >=20 > SELECT > my_jsonb #- '{spec,buildings,0,equipment,selected_inverters}') = #- '{spec,buildings,1,equipment,selected_inverters}' AS my_jsob > FROM my_table >=20 > This feels like a nasty solution, more so.. I may have various number = of dicts in the buildings array. >=20 > Does anyone have some ideas on how I could implement something like = the following? >=20 > SELECT > my_jsonb #- '{spec,buildings,*,equipment,selected_inverters}') = AS my_jsob > FROM my_table >=20 >=20 > 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? >=20 > Here's a sandbox where I put your data and query for examination: = https://www.db-fiddle.com/f/qBhWGyTttT2qqVmw76AJSo/0 = >=20 > Can you please clarify what you're trying to accomplish with the query = (like what output do you want).. >=20 > Thanks, > Steve=20 --Apple-Mail=_584343F8-6FAF-425E-BE89-8B5A9F41756F Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Hi = Steve,

Very strange. = It is supposed to remove the element at path given.

Clean docker compose = with 13.4 PostgreSQL has no problem running exact same SQL and is = successful in removing the key.

=E2=9D=AF psql -h = 127.0.0.1 -U postgres -d postgres -c 'SELECT version();'
Password for user postgres:
  =                     =                     =                     = version
---------------------------------------------------------------= --------------------------------------------------------------------
=
 PostgreSQL 13.4 (Debian 13.4-1.pgdg110+1) on = aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 = 20210110, 64-bit
(1 row)

Screenshot: https://cln.sh/5PLjiL

Running following SQL

DROP TABLE IF = EXISTS jtest;
CREATE = TABLE jtest (jfield jsonb);

INSERT INTO jtest (jfield)
VALUES
  =   ('{"spec":{"id":"485197a6-253a-42b3-9c07-6bac07c02166","buildi= ngs":[{"id":"1b6754b5-c1db-4fdd-af39-32ac173c88cb","equipment":{"selected_= inverters":{"15c9e4a2-5dc7-4017-a09f-1d8e75bdfaef":{"count":1}}}},{"id":"0= c6d0627-9fd9-4989-819c-35743640052d","equipment":{"selected_inverters":{"1= 25a2eb4-f26f-4d07-89fa-f14df9dac7cf":{"count":2}}}}]}}');

SELECT
    1,
  =   jfield #- '{spec,buildings,0,equipment,selected_inve= rters}' #- '{spec,buildings,1,equipment,selected_inverters}'
FROM jtest

UNION

SELECT
    2,
    jfield
FROM jtest;

yields

-[ RECORD 1 ]--------
?column? | 2
?column? | {"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}}}}]}}
-[ RECORD 2 ]--------
?column? | = 1
?column? | {"spec": {"id": = "485197a6-253a-42b3-9c07-6bac07c02166", "buildings": [{"id": = "1b6754b5-c1db-4fdd-af39-32ac173c88cb", "equipment": {}}, {"id": = "0c6d0627-9fd9-4989-819c-35743640052d", "equipment": = {}}]}}

But= anyhow.. 

What I am trying to achieve is to remove a key nested inside = an array, but from all elements.

In the example, I have two buildings in = the buildings array, hence I need to run the #- operation twice, for = first and second element. However the problem is that the array length = across the records in table are variable and am looking to implement = something like = '{spec,buildings,*,equipment,selected_inverters}'.

BR, JP.

On 11 Oct 2021, at 21:09, Steve Midgley <science@misuse.org> = wrote:



On Sun, Oct 10, 2021 at 1:45 PM JP = <janis@puris.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
<= div class=3D"">
Can you please = clarify what you're trying to accomplish with the query (like what = output do you want)..

Thanks,
Steve 

= --Apple-Mail=_584343F8-6FAF-425E-BE89-8B5A9F41756F--