Thread

1. object mapping for json/jsonb columns

   Jan Bernitt <jaanbernitt@gmail.com> — 2024-08-27T12:28:58Z

   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
   

2. Re: object mapping for json/jsonb columns

   Steve Midgley <science@misuse.org> — 2024-08-27T15:34:55Z

   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_tableSET jsonb_column = updated_data.new_jsonb_columnFROM
   updated_dataWHERE my_table.id = updated_data.id;
   
   Is that basically what you've already tried?
   Steve
   

3. Re: object mapping for json/jsonb columns

   Jan Bernitt <jaanbernitt@gmail.com> — 2024-08-27T16:05:00Z

   Hi Steve!
   
   Thanks for the reply.
   
   I think your python script reflects what I want in this particular case.
   I was hoping that there was a generic function to transform values of an
   object where you supply a transformation function that accepts the old
   value to produce the new value.
   Such tasks seem so basic that it is hard to imagine that this isn't
   available but the longer I look at it, it does appear to be the case.
   
   I imagine it like this
   
       jsonb_map_values(object_column, '{value}')
   
   That would use the "value" path of each value to become the new root of
   that entry's value.
   I assume the crux is that SQL has no actual concept of lambda expression
   where a new free variable can be declared in argument.
   That would be a precondition to encode this more clear and with more
   possibilities, like so
   
       jsonb_map_values(object_column, val => val.value)
   
   I have looked at SQL solutions like the one you outlined. At that level I
   guess there are several ways to attack the task.
   But when you look at that SQL this is so far away from communicating the
   intent that I never felt I wanted to sink time into making one of these
   work.
   Also I have a hard time imagining this has anywhere near the performance of
   a projection where we might in reality just move a pointer from the root to
   one of its members.
   So if nothing like this exists in postgreSQL I highly suggest starting to
   design JSON(B) functions that offer this flexibility :)
   
   Best
   Jan
   
   
   Am Di., 27. Aug. 2024 um 17:35 Uhr schrieb Steve Midgley <science@misuse.org
   >:
   
   >
   >
   > 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_tableSET jsonb_column = updated_data.new_jsonb_columnFROM updated_dataWHERE my_table.id = updated_data.id;
   >
   > Is that basically what you've already tried?
   > Steve
   >
   >
   

4. object mapping for json/jsonb columns

   David G. Johnston <david.g.johnston@gmail.com> — 2024-08-27T16:17:29Z

   On Tuesday, August 27, 2024, Jan Bernitt <jaanbernitt@gmail.com> wrote:
   
   >
   > So if nothing like this exists in postgreSQL I highly suggest starting to
   > design JSON(B) functions that offer this flexibility :)
   >
   
   PostgreSQL is both extensible and open source.  I highly suggest this seems
   like a perfect place for some enterprising people to leverage those aspects
   to improve things instead of burdening the core developers with more work.
   Given that pluggable procedural languages already have this kind of tooling
   I’d even hazard to say this is a solved issue for those that go against
   normalization of a relational model.
   
   David J.