Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1siyE6-00ByE5-5i for pgsql-sql@arkaria.postgresql.org; Tue, 27 Aug 2024 15:35:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1siyE3-008ojT-5m for pgsql-sql@arkaria.postgresql.org; Tue, 27 Aug 2024 15:35:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1siyE2-008ojL-OF for pgsql-sql@lists.postgresql.org; Tue, 27 Aug 2024 15:35:19 +0000 Received: from mail-ej1-f43.google.com ([209.85.218.43]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1siyDw-001hkI-Aa for pgsql-sql@lists.postgresql.org; Tue, 27 Aug 2024 15:35:18 +0000 Received: by mail-ej1-f43.google.com with SMTP id a640c23a62f3a-a86a37208b2so509391666b.0 for ; Tue, 27 Aug 2024 08:35:11 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724772910; x=1725377710; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=i/V+Ys8IRCxN/wRi7fwUliykLYqyWqg30oWt1q94HeQ=; b=ZpIZ7HW5P9hOzyNmzF0/SiY7Sa2VPfrrp8OXOL3dgTQLoD+Slrg//joIl62cD3Tm3B WQH5FZwxZZYaLH8hjJ9GDZ1AuZEnrqCIQliZirFoRy9gcVoYGInmwFu80+nvz9J7dAJT 0++aENGRnU7s2JfG58d09XB3VDgButX9/4EKI4ppE1DhCma1yhNdtOzFr5KR9vxRRTC7 D5//EqXKJLSi8F3fDLf4Zzl7FOZAzlIneVSPSrVIurhfh4Dt8z37CG/5cPQ4k6IZF8x9 UWQZ8n2sJEXiIycJz4hBB6E3FD7rtgidCf17hxBmw2nb+8yeTNkSFi8vcF6Ry79B4I+o S3BQ== X-Gm-Message-State: AOJu0Yw1EYDNGGpZ8u8MgqroI+z3bd0eH8mCZiioScPk+TQ0LwnjVxUq PwCuSvtPs3WonNeAvoZLURstlcyEoM0yue7umPUtWk7nEP3qyc9gC7aqWUWM X-Google-Smtp-Source: AGHT+IFCMlWf9D0bV/WJAVaqS3WkhdIIJN9oVS9ZBBH7wm1vtXUVf/ZeKYhdTU/DJkEWgClrINijvQ== X-Received: by 2002:a17:907:6088:b0:a86:96ca:7f54 with SMTP id a640c23a62f3a-a86a52b6521mr973485466b.21.1724772909392; Tue, 27 Aug 2024 08:35:09 -0700 (PDT) Received: from mail-wr1-f54.google.com (mail-wr1-f54.google.com. [209.85.221.54]) by smtp.gmail.com with ESMTPSA id a640c23a62f3a-a86e5878a4asm123677966b.177.2024.08.27.08.35.09 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 27 Aug 2024 08:35:09 -0700 (PDT) Received: by mail-wr1-f54.google.com with SMTP id ffacd0b85a97d-371a9bea8d4so3110732f8f.2 for ; Tue, 27 Aug 2024 08:35:09 -0700 (PDT) X-Received: by 2002:a05:6000:1c4:b0:364:6c08:b9b2 with SMTP id ffacd0b85a97d-373118c83aemr8912803f8f.45.1724772908754; Tue, 27 Aug 2024 08:35:08 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Steve Midgley Date: Tue, 27 Aug 2024 08:34:55 -0700 X-Gmail-Original-Message-ID: Message-ID: Subject: Re: object mapping for json/jsonb columns To: Jan Bernitt Cc: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000008b7bbf0620abfc32" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000008b7bbf0620abfc32 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Aug 27, 2024 at 5:29=E2=80=AFAM Jan Bernitt = wrote: > Hi! > > I hope this is the right group to ask about SQL questions. > > I did quite some research but could not find a satisfactory solution so I > hope to find it by asking this mailing list. > > I have a jsonb column which holds objects used as a map. Let's assume > something like this > > {"key1":{...}, "key2":{...}, ...} > > As you see each value in this object map is itself an object. Let's assum= e > each looks like this > > {"value": 1, "meta": [...]} > > Now I want to get rid of the "meta" part. > > So this might be in a query or as an update where I actually strip the > "meta" attribute in a bulk update. The crux is that I don't know any of t= he > keys or that I want to do this for each of them. > > What seems to be missing is a "map" (projection) function. > > Let's say I want to remove the attribute using #- it seems that I cannot > specify "any name" for the 1. path segment > > mapproperty #- {*,meta} > > That does not work as * is not valid for "any name" > > Similarly, when selecting a path there is [*] for any array element but I > could not find a working solution for any name in an object. > > The only solutions I found were super complicated transformations that ma= p > the JSON to DB records, manipulate that to the shape and selection desire= d > to then put it together to a JSON object again. That seems so overly > complicated and has to be so much slower than a simple mapping function > where I just specify what I want the original value (object) to be mapped > to. In my case I would simply extract "value" to get rid of "meta". > It seems odd to me that something so simple would not be possible while > staying in the JSON(B) world so hopefully I just don't know how. > > Many thanks > Jan > > > I would think that the only way to get the kind of "clean" json map type function is to write it in a python module or similar language. The core function would be something like this that would take your json field and remove the meta portion: return {key: {k: v for k, v in value.items() if k !=3D 'meta'} for key, value in json_obj.items()} I haven't tested either of these snippets but it seems like the cleanest way to use native postgres sql is something like (I'm sure real SQL experts on this list can correct me if I am thinking about this wrong): WITH updated_data AS ( SELECT id, jsonb_object_agg(key, value - 'meta') AS new_jsonb_column FROM my_table, jsonb_each(my_table.jsonb_column) AS obj(key, value) GROUP BY my_table.id )UPDATE my_tableSET jsonb_column =3D updated_data.new_jsonb_columnFROM updated_dataWHERE my_table.id =3D updated_data.id; Is that basically what you've already tried? Steve --0000000000008b7bbf0620abfc32 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Tue, Aug 27, 2024 at 5:29=E2=80=AF= AM Jan Bernitt <jaanbernitt@gma= il.com> wrote:
Hi!

I hope this is the right grou= p to ask about SQL questions.

I did quite some res= earch but could not find a satisfactory solution so I hope to find it by as= king this mailing list.

I have a jsonb column whic= h holds objects used as a map. Let's assume something like this

=C2=A0 =C2=A0 {"key1":{...}, "key2":{= ...}, ...}

As you see each value in this object=C2= =A0map is itself an object. Let's assume each looks like this

=C2=A0 =C2=A0 {"value": 1, "meta": [...= ]}

Now I want to get rid of the "meta" p= art.

So this might be in a query or as an update w= here I actually strip the "meta" attribute in a bulk update. The = crux is that I don't know any of the keys or that I want to do this for= each of them.

What seems to be missing is a "= ;map" (projection) function.

Let's say I = want to remove the attribute using #- it seems that I cannot specify "= any name" for the 1. path segment

=C2=A0 =C2= =A0mapproperty #- {*,meta}

That does not work as * is not valid for = "any name"=C2=A0

Similarly, when selecti= ng a path there is [*] for any array element but I could not find a working= solution for any name in an object.

The only solutions I found were= super complicated transformations that map the JSON to DB records, manipul= ate that to the shape and selection desired to then put it together to a JS= ON object again. That seems so overly complicated and has to be so much slo= wer than a simple mapping function where I just specify what I want the ori= ginal value (object) to be mapped to. In my case I would simply extract &qu= ot;value" to get rid of "meta".=C2=A0
It seems odd to me = that something so simple would not be possible while staying in the JSON(B)= world so hopefully I just don't know how.

Many thanks
Jan


I would think that= the only way to get the kind of "clean" json map type function i= s to write it in a python module or similar language. The core function wou= ld be something like this that would take your json field and remove the me= ta portion:
return {key: {k: v for k, v in valu=
e.items() if k !=3D 'meta'} for key, value in js=
on_obj.items()}
I haven't tested either of these snippets but it = seems like the cleanest way to use native postgres sql is something like (I= 'm sure real SQL experts on this list can correct me if I am thinking a= bout this wrong):
WITH updated_data AS (
  SELECT id,
    jsonb_object_agg(key, value <=
span class=3D"gmail-hljs-operator">- 'meta') AS new_=
jsonb_column
  FROM my_table,
    jsonb_each(my_table.jsonb_column) AS=
 obj(key, value)
  GROUP BY my_table.id
)
UPDATE my_table
SET jsonb_column =3D updated_data.new_jsonb_column
FROM updated_data
WHERE my_table.id =3D updated_data.id;
Is that b= asically what you've already tried?
Ste= ve
=C2=A0
--0000000000008b7bbf0620abfc32--