How to update a JSONB object with a parent in postgresql

Question

I have a database with a table foo containing a column id and a column data with the following data:

{
   "startDate":"2017-07-04",
   "endDate":"2017-07-10",
   "notDelegated":false,
   "sold":false,
   "disableRanking":false,
   "type":"PERIOD"
}

I would like to update this data with a parent rangeData and extract the type property like this:

{
   "rangeData": {
       "startDate":"2017-07-04",
       "endDate":"2017-07-10",
       "notDelegated":false,
       "sold":false,
       "disableRanking":false
   },
   "type":"PERIOD"
}

I tried a lot of things with JSON operators in vain.

Thank you for your answers.

klin · Accepted Answer · 2017-11-14 17:35:41Z

2

Use the function jsonb_build_object() and delete operator:

update foo
set data = jsonb_build_object('rangeData', data- 'type', 'type', data->'type');

In the above function call you are creating a json object with two elements:

key          value
-------------------------
'rangeData'  data- 'type'   json object 'data' from which the key 'type' was removed
'type'       data->'type'   value of 'type' element of json object 'data'

SqlFiddle

edited Nov 14, 2017 at 17:35

answered Nov 13, 2017 at 19:24

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mike Over a year ago

Great!! You saved my time! Thank you so much ;) Just for explanation, you could you please explain the content of jsonb_build_object? The given example in postgresql documentation just indicates that json_build_object('foo',1,'bar',2) gives {"foo": 1, "bar": 2}. Your request seems a little bit complex than this simple given example...

klin Over a year ago

I've added an explanation, hope this helps.

mike Over a year ago

Excellent! Thank you very much for this explanation. I didn't know the syntax data -type.

Josh Beam · Accepted Answer · 2017-11-13 18:55:10Z

You can use jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean]) (see the docs).

It's not very pretty, but I did this (you can run it yourself on this SQLFiddle I made):

SELECT 
  jsonb_set(
    (final.range_data):: jsonb, 
    '{type}', 
    to_jsonb(final.type)
  ) 
FROM 
  (
    SELECT 
      jsonb_set(
        jsonb '{}', '{rangeData}', fields.range_data
      ) AS range_data, 
      fields.type AS type 
    FROM 
      (
        SELECT 
          data.jsonb - 'type' AS range_data, 
          data.jsonb ->> 'type' AS type 
        FROM 
          (
            SELECT 
              '{"startDate":"2017-07-04","endDate":"2017-07-10","notDelegated":false,"sold":false,"disableRanking":false,"type":"PERIOD"}' :: jsonb
          ) data
      ) fields
  ) final;

This will get you your jsonb output of:

{"rangeData": {"startDate":"2017-07-04","endDate":"2017-07-10","notDelegated":false,"sold":false,"disableRanking":false},"type":"PERIOD"}

So you'll end up doing something like:

UPDATE 
  foo 
SET 
  data = (
    SELECT 
      jsonb_set(
        (final.range_data):: jsonb, 
        '{type}', 
        to_jsonb(final.type)
      ) 
    FROM 
      (
        SELECT 
          jsonb_set(
            jsonb '{}', '{rangeData}', fields.range_data
          ) AS range_data, 
          fields.type AS type 
        FROM 
          (
            SELECT 
              data.jsonb - 'type' AS range_data, 
              data.jsonb ->> 'type' AS type 
            FROM 
              (
                SELECT 
                  foo.data
              ) data
          ) fields
      ) final
  );

I haven't run the final UPDATE query, but I did run the naïve SELECT statement and it got me the output as expected.

Also, caveat, I'm new to SQL (and especially the jsonb stuff), so I'm sure there will be a more performant and/or more correct "jsonb-like" way to do this. Depends on your use-case (if it's a one-off migration, or if you need to do this during every live read, etc.)

Thanks for your answer. I made the choice of klin answer - short and efficient

Collectives™ on Stack Overflow

How to update a JSONB object with a parent in postgresql

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related