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

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

return {key: {k: v for k, v in value.items() if k != 'meta'} for key, value in json_obj.items()}

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;