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 1maYJo-0004K0-PV for pgsql-sql@arkaria.postgresql.org; Wed, 13 Oct 2021 07:04:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1maYJn-0008Le-NI for pgsql-sql@arkaria.postgresql.org; Wed, 13 Oct 2021 07:04:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1maYJm-0008LQ-U6 for pgsql-sql@lists.postgresql.org; Wed, 13 Oct 2021 07:04:51 +0000 Received: from mail-lf1-x131.google.com ([2a00:1450:4864:20::131]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1maYJj-0005la-Ni for pgsql-sql@lists.postgresql.org; Wed, 13 Oct 2021 07:04:49 +0000 Received: by mail-lf1-x131.google.com with SMTP id u18so7269165lfd.12 for ; Wed, 13 Oct 2021 00:04:47 -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=QsV6e6Yjn/BgXB2TxFx31s1u/8T9au4dGFlVDZqMGjw=; b=u+p166S6SYLg+x/GG3xrEeH/BwmVDF2maiFsSKqut8rDuJ/iOVSxyx8bH6sfHol6oY EOwkzrr56NVq1/2M9RMh0hDMzM/TfEPTZjLYWyQcWO+Cy3pDttfF8KFvFJq84Xk76uaC rvSJCRh1HMtD+ufJzwn6rTZ7bMMDyCzrfxYTPkbxIozt46R/r9uiVFkv0wXXF1Sjkrld IsLwFEo0PtJEPyR1NXuvc0dAexXsMLpQRZdNrnTlfmEt6bxUennW+FVh6V39kJOikpBA YQ8Ncd7YcPB2Skwb26RRC0MfG6mf52youn3H0+lWkTtyKGpLkYps+FL1aAC6EhqcQQ29 zENA== 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=QsV6e6Yjn/BgXB2TxFx31s1u/8T9au4dGFlVDZqMGjw=; b=b0dbrL4i3j4VLDDT43NoePMmgBIrmR6kShGt4LzNyK6AEqa9f/v2Tg/GmItT8a9NiE y9LNWwzJmBkWQOYw6EqKdZUevmryh8P5ckSZPBF0KdW2gfgWKfv894jQLLhq2sM8nDT+ JK9ZWksO4YXI0tM25kLLfWE5aVUZgKFvB0dh9qsULYfl8RnpO9+MGVJAjlqWHOcIW3F7 x+DeBN3z7bO800Ekl89X6tXTIr+jS1lLzZc1NZU1Rt/5ql71f1WzJ4ZU1L5GxZ81R0uS W7mxeytAf7B0WO9bmdEPuZQ/d/mMii6PXulHMycKhiw/aKRtf86X1UfqATjNHd3rymFp B5rA== X-Gm-Message-State: AOAM531ev9sRDPVHR4AxwmC+kqjRz29n+IsfEUl72UTX/MISAp90WYe5 vwPa+osU19qbhhlwPHtkz9w8dw== X-Google-Smtp-Source: ABdhPJzHXH6VgByf3XQ5Hv54JShNulKxh3YuLwJdHg4A9GXEiRdrwlOhaBd6ifotAKHGWrgnEmPecw== X-Received: by 2002:a05:6512:acb:: with SMTP id n11mr37605468lfu.222.1634108685782; Wed, 13 Oct 2021 00:04:45 -0700 (PDT) Received: from smtpclient.apple (host-37-191-207-235.lynet.no. [37.191.207.235]) by smtp.gmail.com with ESMTPSA id s17sm1214262ljc.95.2021.10.13.00.04.41 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 13 Oct 2021 00:04:41 -0700 (PDT) From: JP Message-Id: <294D5CF5-BD99-4953-AB0F-AE06C42E4F7B@puris.lv> Content-Type: multipart/alternative; boundary="Apple-Mail=_CCF8FA82-2BB4-435C-A56A-A155161D592B" 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: Wed, 13 Oct 2021 09:04:41 +0200 In-Reply-To: Cc: pgsql-sql , Steve Midgley To: bzm@2bz.de 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=_CCF8FA82-2BB4-435C-A56A-A155161D592B Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi Steve and Boris, Steve, I was also considering a stored procedure and generate the SQL = necessary to remove every element via loop. Thank you for the effort! Much appreciated. Boris, the code you've presented was almost what I needed it to do, = thank you very much! I've modified it a bit to my use case and settled on=20 SELECT JSONB_SET( my_jsonb, '{spec, buildings}', (SELECT JSONB_AGG(value #- '{equipment,selected_inverters}') = FROM JSONB_ARRAY_ELEMENTS((my_jsonb['spec']['buildings']))) ) AS my_jsonb FROM my_table This basically achieves same thing as something like following would my_jsonb #- '{spec,buildings,*,equipment,selected_inverters}') AS = my_jsob Including DDLs and data prep DROP TABLE IF EXISTS my_table; CREATE TABLE my_table ( my_jsonb jsonb ); INSERT INTO my_table (my_jsonb) 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 JSONB_SET( my_jsonb, '{spec, buildings}', (SELECT JSONB_AGG(value #- '{equipment,selected_inverters}') = FROM JSONB_ARRAY_ELEMENTS((my_jsonb['spec']['buildings']))) ) AS my_jsonb FROM my_table BR, JP. > On 13 Oct 2021, at 02:17, Steve Midgley wrote: >=20 >=20 >=20 > On Tue, Oct 12, 2021 at 12:44 PM JP > wrote: > Hi Steve, >=20 > Very strange. It is supposed to remove the element at path given. >=20 > Clean docker compose with 13.4 PostgreSQL has no problem running exact = same SQL and is successful in removing the key. >=20 > =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) >=20 > Screenshot: https://cln.sh/5PLjiL >=20 > Running following SQL >=20 > DROP TABLE IF EXISTS jtest; > CREATE TABLE jtest (jfield jsonb); >=20 > 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}}}}]}}'); >=20 > SELECT > 1, > jfield #- '{spec,buildings,0,equipment,selected_inverters}' #- = '{spec,buildings,1,equipment,selected_inverters}' > FROM jtest >=20 > UNION >=20 > SELECT > 2, > jfield > FROM jtest; >=20 > yields >=20 > -[ 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": {}}]}} >=20 > But anyhow..=20 >=20 > What I am trying to achieve is to remove a key nested inside an array, = but from all elements. >=20 > 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}'. >=20 > BR, JP. >=20 >> 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 >=20 >=20 > Maybe something buggy with the db-fiddle Pg v13 system (not patched to = current?)..=20 >=20 > Anyway, your goal is to remove an element, but it sounds like your = solution is to name every element but the one you want? Is that = acceptable? And basically in this example, you want to remove the = "count" field from the output? >=20 > I played around a bit without success, but I think one idea would be = match the parent of count via a regex using a json path query and remove = count that way.. Sorry I can't be more help, >=20 > I also found this function which claims to do what you want: = https://stackoverflow.com/questions/23490965/postgresql-remove-attribute-f= rom-json-column/23491408 = >=20 > Steve > p.s. Text of function for the curious > CREATE OR REPLACE FUNCTION remove_key(json_in json, key_name text) > RETURNS json AS $$ > DECLARE item json; > DECLARE fields hstore; > BEGIN > -- Initialize the hstore with desired key being set to NULL > fields :=3D hstore(key_name,NULL); >=20 > -- Parse through Input Json and push each key into hstore=20 > FOR item IN SELECT row_to_json(r.*) FROM json_each_text(json_in) AS = r > LOOP > --RAISE NOTICE 'Parsing Item % %', item->>'key', item->>'value'; > fields :=3D (fields::hstore || hstore(item->>'key', = item->>'value')); > END LOOP; > --RAISE NOTICE 'Result %', hstore_to_json(fields); > -- Remove the desired key from store > fields :=3D fields-key_name; >=20 > RETURN hstore_to_json(fields); > END; > $$ LANGUAGE plpgsql > SECURITY DEFINER > STRICT; --Apple-Mail=_CCF8FA82-2BB4-435C-A56A-A155161D592B Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Hi = Steve and Boris,

Steve, I was also considering a stored procedure and generate = the SQL necessary to remove every element via loop.
Thank you for the effort! Much appreciated.

Boris, the code you've = presented was almost what I needed it to do, thank you very = much!
I've modified it a bit to my use case and = settled on 

SELECT
    JSONB_SET(
            my_jsonb,
            '{spec, = buildings}',
          =   (SELECT JSONB_AGG(value #- '{equipment,selected= _inverters}') FROM JSONB_ARRAY_ELEMENTS((my_jsonb['spec']['build= ings'])))
        = ) AS my_jsonb
FROM my_table

This basically achieves = same thing as something like following would

my_jsonb #- = '{spec,buildings,*,equipment,selected_inverters}')  AS = my_jsob

Including DDLs and data = prep

DROP TABLE IF = EXISTS my_table;
CREATE TABLE my_table
(
    my_jsonb jsonb
);

INSERT = INTO my_table (my_jsonb)
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
    JSONB_SET(
            my_jsonb,
            '{spec, = buildings}',
          =   (SELECT JSONB_AGG(value #- '{equipment,selected= _inverters}') FROM JSONB_ARRAY_ELEMENTS((my_jsonb['spec']['build= ings'])))
        = ) AS my_jsonb
FROM my_table

BR, JP.

On 13 Oct 2021, at 02:17, Steve Midgley <science@misuse.org> = wrote:



On Tue, Oct 12, 2021 at = 12:44 PM JP <janis@puris.lv> wrote:
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-1d8e= 75bdfaef": {
            =                 "count": 1
                =         }
  =                   }
  =               }
  =           },
  =           {
  =               "id": = "0c6d0627-9fd9-4989-819c-35743640052d",
    =             "equipment": {
                =     "selected_inverters": {
                =         "125a2eb4-f26f-4d07-89fa-f14d= f9dac7cf": {
            =                 "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)..



Maybe something buggy with the = db-fiddle Pg v13 system (not patched to current?).. 

Anyway, your goal is to = remove an element, but it sounds like your solution is to name every = element but the one you want? Is that acceptable? And basically in this = example, you want to remove the "count" field from the output?

I played around a bit = without success, but I think one idea would be match the parent of count = via a regex using a json path query and remove count that way.. Sorry I = can't be more help,

I also found this function which claims to do what you = want: https://stackoverflow.com/questions/23490965/postgresql-remove-= attribute-from-json-column/23491408

Steve
p.s. Text of = function for the curious
CREATE OR REPLACE FUNCTION remove_key(json_in json, key_name text)
 RETURNS json AS $$
 DECLARE item json;
 DECLARE fields hstore;
BEGIN
 -- Initialize the hstore with desired =
key being set to NULL
 fields :=3D hstore(key_name,NULL);

 -- Parse through Input Json and push =
each key into hstore 
 FOR item IN  SELECT row_to_json(r.*) FROM json_each_text(json_in) =
AS r
 LOOP
   --RAISE NOTICE 'Parsing Item % %', =
item->>'key', item->>'value';
   fields :=3D (fields::hstore || hstore(item->>'key', item->>'value'));
 END LOOP;
 --RAISE NOTICE 'Result %', =
hstore_to_json(fields);
 -- Remove the desired key from =
store
 fields :=3D fields-key_name;

 RETURN hstore_to_json(fields);
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
STRICT;

= --Apple-Mail=_CCF8FA82-2BB4-435C-A56A-A155161D592B--