From: JP <janis@puris.lv>
To: Steve Midgley <science@misuse.org>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Removing JSONB key across all elements of nested array
Date: Tue, 12 Oct 2021 21:44:07 +0200
Message-ID: <E1796301-B2D8-498F-9C7D-4337D3BFD259@puris.lv> (raw)
In-Reply-To: <CAJexoSJjC=nnNng0Lrc2pBegbt9k5rZ-eDeATynBh6setfyQrA@mail.gmail.com>
References: <E1755008-044A-4C24-A18C-AAF04B52ED28@puris.lv>
<CAJexoSJjC=nnNng0Lrc2pBegbt9k5rZ-eDeATynBh6setfyQrA@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: janis@puris.lv, science@misuse.org, pgsql-sql@lists.postgresql.org
Subject: Re: Removing JSONB key across all elements of nested array
In-Reply-To: <E1796301-B2D8-498F-9C7D-4337D3BFD259@puris.lv>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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