MIME-Version: 1.0
From: Jan Bernitt <jaanbernitt@gmail.com>
Date: Tue, 27 Aug 2024 14:28:58 +0200
Message-ID: 
 <CA+W24BU2=w9DSH9Uq6eSiEniQN7SKmrRGSYLpVVPZrGPc2JcZw@mail.gmail.com>
Subject: object mapping for json/jsonb columns
To: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006a86790620a9632f"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BW24BU2%3Dw9DSH9Uq6eSiEniQN7SKmrRGSYLpVVPZrGPc2JcZw%40mail.gmail.com>
Precedence: bulk

--0000000000006a86790620a9632f
Content-Type: text/plain; charset="UTF-8"

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

--0000000000006a86790620a9632f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi!<div><br></div><div>I hope this is the right group to a=
sk about SQL questions.</div><div><br></div><div>I did quite some research =
but could not find a satisfactory solution so I hope to find it by asking t=
his mailing list.</div><div><br></div><div>I have a jsonb column which hold=
s objects used as a map. Let&#39;s assume something like this</div><div><br=
></div><div>=C2=A0 =C2=A0 {&quot;key1&quot;:{...}, &quot;key2&quot;:{...}, =
...}</div><div><br></div><div>As you see each value in this object=C2=A0map=
 is itself an object. Let&#39;s assume each looks like this</div><div><br><=
/div><div>=C2=A0 =C2=A0 {&quot;value&quot;: 1, &quot;meta&quot;: [...]}</di=
v><div><br></div><div>Now I want to get rid of the &quot;meta&quot; part.</=
div><div><br></div><div>So this might be in a query or as an update where I=
 actually strip the &quot;meta&quot; attribute in a bulk update. The crux i=
s that I don&#39;t know any of the keys or that I want to do this for each =
of them.</div><div><br></div><div>What seems to be missing is a &quot;map&q=
uot; (projection) function.</div><div><br></div><div>Let&#39;s say I want t=
o remove the attribute using #- it seems that I cannot specify &quot;any na=
me&quot; for the 1. path segment</div><div><br></div><div>=C2=A0 =C2=A0mapp=
roperty #- {*,meta}<br><br>That does not work as * is not valid for &quot;a=
ny name&quot;=C2=A0</div><div><br></div><div>Similarly, when selecting a pa=
th there is [*] for any array element but I could not find a working soluti=
on for any name in an object.<br><br>The only solutions I found were super =
complicated transformations that map the JSON to DB records, manipulate tha=
t to the shape and selection desired to then put it together to a JSON obje=
ct again. That seems so overly complicated and has to be so much slower tha=
n a simple mapping function where I just specify what I want the original v=
alue (object) to be mapped to. In my case I would simply extract &quot;valu=
e&quot; to get rid of &quot;meta&quot;.=C2=A0<br>It seems odd to me that so=
mething so simple would not be possible while staying in the JSON(B) world =
so hopefully I just don&#39;t know how.<br><br>Many thanks<br>Jan</div><div=
><br></div><div><br></div></div>

--0000000000006a86790620a9632f--