Update separate table columns from nested jsonb attributes

Question

I'm trying to run a Postgres SQL call to migrate data from a jsonb field to separate fields for each attribute, and for some reason the process is not working for the nested fields.

{
  "basis": "relative",
  "defined": "2023-06-21T10:21:16.979",
  "absolute": "2023-06-30T00:00:00.000",
  "relative": {
    "value": 0,
    "period": "month"
  }
}

The SQL being run is:

update gd_task_instance
set 
scheduled_basis = CAST(scheduled->>'basis' AS TEXT),
scheduled_defined = CAST(scheduled->>'defined' as TIMESTAMP),
scheduled_absolute = CAST(scheduled->>'absolute' AS DATE),
scheduled_relative_value = CAST(scheduled->'relative->value' AS INT),
scheduled_relative_period = CAST(scheduled->'relative->>period' AS TEXT);

The last two fields 'scheduled_relative_value' and 'scheduled_relative_period' are not being set.

Any suggestions?

You meant scheduled -> 'relative' -> 'value' but wrote scheduled -> 'relative->value'. — Bergi
– Bergi, Commented Jan 7 at 7:56

Erwin Brandstetter · Accepted Answer · 2025-01-07 13:45:29Z

1

Replace

'relative->value'
'relative->>period'

with:

'relative' ->> 'value'
'relative' ->> 'period'

Better

UPDATE gd_task_instance
SET    scheduled_basis           =  t.scheduled ->> 'basis'
     , scheduled_defined         = (t.scheduled ->> 'defined')::timestamp
     , scheduled_absolute        = (t.scheduled ->> 'absolute')::date
     , scheduled_relative_value  = (t.scheduled #>> '{relative,value}')::int
     , scheduled_relative_period =  t.scheduled #>> '{relative,period}'
FROM (
   SELECT json
   '{
     "basis": "relative",
     "defined": "2023-06-21T10:21:16.979",
     "absolute": "2023-06-30T00:00:00.000",
     "relative": {
       "value": 0,
       "period": "month"
      }
    }'
   ) t(scheduled)
WHERE  id = 1
RETURNING *
;

fiddle

For two or more nested levels you can shorten the syntax:

t.scheduled -> 'relative' ->> 'period'

... with the #>> operator:

t.scheduled #>> '{relative,period}'

Casting to text is redundant, that's the result type already.

edited Jan 7 at 13:45

answered Jan 7 at 7:24

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

user2868835 Jan 7 at 8:51

One problem I've found is that the nested 'relative.period' attributes are being returned as double quoted strings. How do I get rid of the double quotes?

Erwin Brandstetter Jan 7 at 13:47

Note ->> instead of ->. The former returns text, the latter json(b). Consider the added example and fiddle.

user2868835 · Accepted Answer · 2025-01-07 07:24:00Z

0

Found the answer...

update gd_task_instance
set 
scheduled_basis = CAST(scheduled->>'basis' AS TEXT),
scheduled_defined = CAST(scheduled->>'defined' as TIMESTAMP),
scheduled_absolute = CAST(scheduled->>'absolute' AS DATE),
scheduled_relative_value = CAST(scheduled['relative']['value'] AS INT),
scheduled_relative_period = CAST(scheduled['relative']['period'] AS TEXT);

answered Jan 7 at 7:24

user2868835

1,7145 gold badges27 silver badges56 bronze badges

Collectives™ on Stack Overflow

Update separate table columns from nested jsonb attributes

2 Answers 2

Better

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Better

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related