public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jan Bernitt <[email protected]>
To: Steve Midgley <[email protected]>
Cc: [email protected]
Subject: Re: object mapping for json/jsonb columns
Date: Tue, 27 Aug 2024 18:05:00 +0200
Message-ID: <CA+W24BXXXQLT_Yak36DHQeOi9huEnyZX8EzvC5zU_OeVP7RU-g@mail.gmail.com> (raw)
In-Reply-To: <CAJexoSJqBGfWjwOSF0nn36qs9vbkpNQtPs-BG5=KFfmknLgWNQ@mail.gmail.com>
References: <CA+W24BU2=w9DSH9Uq6eSiEniQN7SKmrRGSYLpVVPZrGPc2JcZw@mail.gmail.com>
<CAJexoSJqBGfWjwOSF0nn36qs9vbkpNQtPs-BG5=KFfmknLgWNQ@mail.gmail.com>
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 protected]
>:
>
>
> On Tue, Aug 27, 2024 at 5:29 AM Jan Bernitt <[email protected]> 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 != '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
>
>
reply
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: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: object mapping for json/jsonb columns
In-Reply-To: <CA+W24BXXXQLT_Yak36DHQeOi9huEnyZX8EzvC5zU_OeVP7RU-g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox