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 1siyh3-00C6qF-Hd for pgsql-sql@arkaria.postgresql.org; Tue, 27 Aug 2024 16:05:17 +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 1siyh1-009HPZ-A3 for pgsql-sql@arkaria.postgresql.org; Tue, 27 Aug 2024 16:05:15 +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 1siyh0-009HPP-T0 for pgsql-sql@lists.postgresql.org; Tue, 27 Aug 2024 16:05:15 +0000 Received: from mail-oi1-x233.google.com ([2607:f8b0:4864:20::233]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1siygy-001hvt-Fy for pgsql-sql@lists.postgresql.org; Tue, 27 Aug 2024 16:05:14 +0000 Received: by mail-oi1-x233.google.com with SMTP id 5614622812f47-3db1e21b0e4so585978b6e.2 for ; Tue, 27 Aug 2024 09:05:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724774712; x=1725379512; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=Ex25jQZMIA/d82wRhsfQJR7J+ToPXOvOUfkkIM8vS5Q=; b=VQshrTOLSsXgwL5zxydJkXMoFexjAp2NGe+uGU2o4MEewyiNBwCAj+RjN8/uZH6miv BszupHzItJM9VzJ+wQvrrCk4I2KPHVtIY1R9UgEG/6TbHOVHfB+S3MUgWUzvDCB9UtZD PecA6QJ+w4GH0B9mdD24WgDHs3aXhrQ4mb3oCwaLz0YQ8PnGcoPi6YqL3Yx8MpnaJLqt KzouLB3KNpB2Dfb00Cog7j4HJKWsQzfMZm4rVfIcOMN5UcUavpWGzn79n6XDQHA3Yk9S noqyMWtTU/lB3FS//XfynnffN2uO3EBGqqu6WrnJIIWETSVT5sW8LKkCWUZ2xAGukZqz o5rA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724774712; x=1725379512; 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=Ex25jQZMIA/d82wRhsfQJR7J+ToPXOvOUfkkIM8vS5Q=; b=JFNL2w7Qb6H8yjfRZ+N/mJOAQKIwOw6j+agkrXI5CbyWz0tKIxXVD0ou/us0RZfwlY bRK8+xR17l3Q52LPSgo6/k8exHU9szwSJg+ljXHqrd95TrsZwSGNeH1tDRlvFmJ6XqKK C1dzNOS834Q/mAts0sHL+IDZuJdzBy2NsjUR3tW8TEe8gqeshdRr8rka/SweInimFcuH Wdig33/PKMXoC0bNUITLMlki+NJRgPkJG97vbkUIl8u7TI0LvEEAHzdAK7RCRml7ltvl 40Aj4iclvfh4n4txpjjZQelMtFL1VOdcLJKu5H9uhe9yYgWIRr8Pm9EjbIpUUIs8qLIB Amxg== X-Gm-Message-State: AOJu0Ywjxf0at+q9Q6e3yRziH6L8OVPqEi3C63+4t4AJJUSNctFkvEtU FJcGQI+UHKIQm3VBJvidCCdeZ1JzbgHJQA6ymbv/BnBrYNXW0Lok/+oq0APwypWpYs3m0PrjiwG AxQq1462VOg4BuqMkBbV+dZMcKPsj/hqbBzY= X-Google-Smtp-Source: AGHT+IGHvFhiWDcBLcMDL8G11F4/hQl1kmiuqKdlU/iFOF5qjlLIftJvHWiHdZr7QzznBsmiuPDRPa+a/P6DtvACYfk= X-Received: by 2002:a05:6808:1414:b0:3d6:3450:7fed with SMTP id 5614622812f47-3de2a8afb3amr14836838b6e.39.1724774711698; Tue, 27 Aug 2024 09:05:11 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Jan Bernitt Date: Tue, 27 Aug 2024 18:05:00 +0200 Message-ID: Subject: Re: object mapping for json/jsonb columns To: Steve Midgley Cc: pgsql-sql@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000022f900620ac6839" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000022f900620ac6839 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Steve! Thanks for the reply. I think your python script reflects what I want in this particular case. I was hoping that there was a generic function to transform values of an object where you supply a transformation function that accepts the old value to produce the new value. Such tasks seem so basic that it is hard to imagine that this isn't available but the longer I look at it, it does appear to be the case. I imagine it like this jsonb_map_values(object_column, '{value}') That would use the "value" path of each value to become the new root of that entry's value. I assume the crux is that SQL has no actual concept of lambda expression where a new free variable can be declared in argument. That would be a precondition to encode this more clear and with more possibilities, like so jsonb_map_values(object_column, val =3D> val.value) I have looked at SQL solutions like the one you outlined. At that level I guess there are several ways to attack the task. But when you look at that SQL this is so far away from communicating the intent that I never felt I wanted to sink time into making one of these work. Also I have a hard time imagining this has anywhere near the performance of a projection where we might in reality just move a pointer from the root to one of its members. So if nothing like this exists in postgreSQL I highly suggest starting to design JSON(B) functions that offer this flexibility :) Best Jan Am Di., 27. Aug. 2024 um 17:35 Uhr schrieb Steve Midgley : > > > 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 >> assume 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 = 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 >> >> 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 >> map the JSON to DB records, manipulate that to the shape and selection >> desired 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, v= alue 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 exper= ts > 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 up= dated_dataWHERE my_table.id =3D updated_data.id; > > Is that basically what you've already tried? > Steve > > --000000000000022f900620ac6839 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Steve!

Thanks for the reply.

= I think your python script reflects what I want in this particular case. I was hoping that there was a generic function to transform values of an = object where you supply a transformation function that accepts the old valu= e to produce the new value.
Such tasks seem so basic that it is hard to = imagine that this isn't available but the longer I look at it, it does = appear=C2=A0to be the case.

I imagine it like this

=C2=A0 =C2= =A0 jsonb_map_values(object_column, '{value}')

That would us= e the "value" path of each value to become the new root of that e= ntry's value.=C2=A0
I assume the crux is that SQL has no actual conc= ept of lambda expression where a new free variable can be declared in argum= ent.
That would be a precondition to encode this more clear and w= ith more possibilities, like so

=C2=A0 =C2=A0 json= b_map_values(object_column, val =3D> val.value)

I have looked at SQL solutions like the one you outlined. At that level I = guess there are several ways to attack the task.
But when you look at th= at SQL this is so far away from communicating the intent that I never felt = I wanted to sink time into making one of these work.
Also I have a hard = time imagining this has anywhere near the performance of a projection where= we might in reality just move a pointer from the root to one of its member= s.
So if nothing like this exists in postgreSQL I highly suggest = starting to design JSON(B) functions that offer this flexibility :)

= Best
Jan


Am Di., 27. Aug. 2024 um 17:35=C2=A0Uhr sch= rieb Steve Midgley <science@misuse= .org>:


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

I hope this i= s the right group to ask about SQL questions.

I di= d quite some research but could not find a satisfactory solution so I hope = to find it by asking this mailing list.

I have a j= sonb column which holds objects used as a map. Let's assume something l= ike this

=C2=A0 =C2=A0 {"key1":{...}, &q= uot;key2":{...}, ...}

As you see each value i= n this object=C2=A0map is itself an object. Let's assume each looks lik= e this

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

Now I want to get rid of the &q= uot;meta" part.

So this might be in a query o= r as an update where I actually strip the "meta" attribute in a b= ulk update. The crux is that I don't know any of the keys or that I wan= t to do this for each of them.

What seems to be mi= ssing is a "map" (projection) function.

= Let's say I want to remove the attribute using #- it seems that I canno= t 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

Similarl= y, 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 solutio= ns I found were super complicated transformations that map the JSON to DB r= ecords, manipulate that to the shape and selection desired to then put it t= ogether 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 sim= ply extract "value" to get rid of "meta".=C2=A0
It s= eems 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 t= hanks
Jan


I w= ould 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 co= re function would be something like this that would take your json field an= d 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 a= bout 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<=
/span>)
  GROUP BY my_table.id
)
UPDATE my_table
SET jsonb_column =3D updated_data.new_jsonb_colum=
n
FROM updated_data
WHERE my_tabl=
e.id =3D updated_data.id;
Is that b= asically what you've already tried?
Ste= ve
=C2=A0
--000000000000022f900620ac6839--