Re: object mapping for json/jsonb columns - Mailing list pgsql-sql

On Tue, Aug 27, 2024 at 5:29 AM Jan Bernitt <jaanbernitt@gmail.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_table
SET jsonb_column = updated_data.new_jsonb_column
FROM updated_data
WHERE my_table.id = updated_data.id;

Is that basically what you've already tried?

Steve