public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jan Bernitt <[email protected]>
To: [email protected]
Subject: object mapping for json/jsonb columns
Date: Tue, 27 Aug 2024 14:28:58 +0200
Message-ID: <CA+W24BU2=w9DSH9Uq6eSiEniQN7SKmrRGSYLpVVPZrGPc2JcZw@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: object mapping for json/jsonb columns
  In-Reply-To: <CA+W24BU2=w9DSH9Uq6eSiEniQN7SKmrRGSYLpVVPZrGPc2JcZw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox