public inbox for [email protected]
help / color / mirror / Atom feedobject mapping for json/jsonb columns
4+ messages / 3 participants
[nested] [flat]
* object mapping for json/jsonb columns
@ 2024-08-27 12:28 Jan Bernitt <[email protected]>
2024-08-27 15:34 ` Re: object mapping for json/jsonb columns Steve Midgley <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Jan Bernitt @ 2024-08-27 12:28 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: object mapping for json/jsonb columns
2024-08-27 12:28 object mapping for json/jsonb columns Jan Bernitt <[email protected]>
@ 2024-08-27 15:34 ` Steve Midgley <[email protected]>
2024-08-27 16:05 ` Re: object mapping for json/jsonb columns Jan Bernitt <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Steve Midgley @ 2024-08-27 15:34 UTC (permalink / raw)
To: Jan Bernitt <[email protected]>; +Cc: [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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: object mapping for json/jsonb columns
2024-08-27 12:28 object mapping for json/jsonb columns Jan Bernitt <[email protected]>
2024-08-27 15:34 ` Re: object mapping for json/jsonb columns Steve Midgley <[email protected]>
@ 2024-08-27 16:05 ` Jan Bernitt <[email protected]>
2024-08-27 16:17 ` object mapping for json/jsonb columns David G. Johnston <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Jan Bernitt @ 2024-08-27 16:05 UTC (permalink / raw)
To: Steve Midgley <[email protected]>; +Cc: [email protected]
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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* object mapping for json/jsonb columns
2024-08-27 12:28 object mapping for json/jsonb columns Jan Bernitt <[email protected]>
2024-08-27 15:34 ` Re: object mapping for json/jsonb columns Steve Midgley <[email protected]>
2024-08-27 16:05 ` Re: object mapping for json/jsonb columns Jan Bernitt <[email protected]>
@ 2024-08-27 16:17 ` David G. Johnston <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2024-08-27 16:17 UTC (permalink / raw)
To: Jan Bernitt <[email protected]>; +Cc: Steve Midgley <[email protected]>; [email protected] <[email protected]>
On Tuesday, August 27, 2024, Jan Bernitt <[email protected]> wrote:
>
> So if nothing like this exists in postgreSQL I highly suggest starting to
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-08-27 16:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-27 12:28 object mapping for json/jsonb columns Jan Bernitt <[email protected]>
2024-08-27 15:34 ` Steve Midgley <[email protected]>
2024-08-27 16:05 ` Jan Bernitt <[email protected]>
2024-08-27 16:17 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox