Re: object mapping for json/jsonb columns

On Tue, Aug 27, 2024 at 5:29 AM Jan Bernitt <jaanbernitt(at)gmail(dot)com> 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