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
