Subject: object mapping for json/jsonb columns

From: Jan Bernitt <[email-masked]>
Date: Tue, 27 Aug 2024 14:28:58 +0200

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

---

From: Steve Midgley <[email-masked]>
Date: Tue, 27 Aug 2024 08:34:55 -0700

On Tue, Aug 27, 2024 at 5:29 AM Jan Bernitt <[email-masked]> wrote:

> 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 != '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 = updated_data.new_jsonb_columnFROM
updated_dataWHERE my_table.id = updated_data.id;

Is that basically what you've already tried?
Steve

---

From: Jan Bernitt <[email-masked]>
Date: Tue, 27 Aug 2024 18:05:00 +0200

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 => 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 <[email-masked]
>:

>> As you see each value in this object map is itself an object. Let's
>> assume each looks like this
>>
>> 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".
> return {key: {k: v for k, v in value.items() if k != 'meta'} for key, value in json_obj.items()}
>
> )UPDATE my_tableSET jsonb_column = updated_data.new_jsonb_columnFROM updated_dataWHERE my_table.id = updated_data.id;
>

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 27 Aug 2024 09:17:29 -0700

> design JSON(B) functions that offer this flexibility :)

PostgreSQL is both extensible and open source.  I highly suggest this seems
like a perfect place for some enterprising people to leverage those aspects
to improve things instead of burdening the core developers with more work.
Given that pluggable procedural languages already have this kind of tooling
I’d even hazard to say this is a solved issue for those that go against
normalization of a relational model.

David J.