CODEWITHSUNDEEP
jsonpostgresqljsonb
mike
mike

Reputation: 425

How to update a JSONB object with a parent in postgresql

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.

Upvotes: 3

Views: 1307

Answers (2)

klin
klin

Reputation: 121754

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

Upvotes: 2

Josh Beam
Josh Beam

Reputation: 19792

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.)

Upvotes: 0

Related Questions