object mapping for json/jsonb columns

Поиск
Список
Период
Сортировка
От Jan Bernitt
Тема object mapping for json/jsonb columns
Дата
Msg-id CA+W24BU2=w9DSH9Uq6eSiEniQN7SKmrRGSYLpVVPZrGPc2JcZw@mail.gmail.com
обсуждение исходный текст
Ответы Re: object mapping for json/jsonb columns
Список 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


В списке pgsql-sql по дате отправления: