pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRemoving JSONB key across all elements of nested array
5+ messages / 2 participants
[nested] [flat]
* Removing JSONB key across all elements of nested array
@ 2021-10-10 20:45 JP <janis@puris.lv>
0 siblings, 1 reply; 5+ messages in thread
From: JP @ 2021-10-10 20:45 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Removing JSONB key across all elements of nested array
@ 2021-10-11 19:09 Steve Midgley <science@misuse.org>
parent: JP <janis@puris.lv>
0 siblings, 1 reply; 5+ messages in thread
From: Steve Midgley @ 2021-10-11 19:09 UTC (permalink / raw)
To: JP <janis@puris.lv>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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
Can you please clarify what you're trying to accomplish with the query
(like what output do you want)..
Thanks,
Steve
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Removing JSONB key across all elements of nested array
@ 2021-10-12 19:44 JP <janis@puris.lv>
parent: Steve Midgley <science@misuse.org>
0 siblings, 1 reply; 5+ messages in thread
From: JP @ 2021-10-12 19:44 UTC (permalink / raw)
To: Steve Midgley <science@misuse.org>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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.
❯ 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 <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..
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 <mailto: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 <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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Removing JSONB key across all elements of nested array
@ 2021-10-13 00:17 Steve Midgley <science@misuse.org>
parent: JP <janis@puris.lv>
0 siblings, 1 reply; 5+ messages in thread
From: Steve Midgley @ 2021-10-13 00:17 UTC (permalink / raw)
To: JP <janis@puris.lv>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
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.
>
> ❯ 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..
>
> 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
>
> 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 := 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 := (fields::hstore || hstore(item->>'key', item->>'value'));
END LOOP;
--RAISE NOTICE 'Result %', hstore_to_json(fields);
-- Remove the desired key from store
fields := fields-key_name;
RETURN hstore_to_json(fields);END;
$$ LANGUAGE plpgsql
SECURITY DEFINER
STRICT;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Removing JSONB key across all elements of nested array
@ 2021-10-13 07:04 JP <janis@puris.lv>
parent: Steve Midgley <science@misuse.org>
0 siblings, 0 replies; 5+ messages in thread
From: JP @ 2021-10-13 07:04 UTC (permalink / raw)
To: bzm@2bz.de; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>; Steve Midgley <science@misuse.org>
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']['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 <science@misuse.org> wrote:
>
>
>
> On Tue, Oct 12, 2021 at 12:44 PM JP <janis@puris.lv <mailto: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.
>
> ❯ 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 <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..
>
> 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 <mailto:science@misuse.org>> wrote:
>>
>>
>>
>> On Sun, Oct 10, 2021 at 1:45 PM JP <janis@puris.lv <mailto: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 <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)..
>>
>
>
> 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 <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 := 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 := (fields::hstore || hstore(item->>'key', item->>'value'));
> END LOOP;
> --RAISE NOTICE 'Result %', hstore_to_json(fields);
> -- Remove the desired key from store
> fields := fields-key_name;
>
> RETURN hstore_to_json(fields);
> END;
> $$ LANGUAGE plpgsql
> SECURITY DEFINER
> STRICT;
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-10-13 07:04 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-10 20:45 Removing JSONB key across all elements of nested array JP <janis@puris.lv>
2021-10-11 19:09 ` Steve Midgley <science@misuse.org>
2021-10-12 19:44 ` JP <janis@puris.lv>
2021-10-13 00:17 ` Steve Midgley <science@misuse.org>
2021-10-13 07:04 ` JP <janis@puris.lv>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox