PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Supabase/Postgres – move nested jsonb attribute values into distinct fields – not working

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    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);

  2. 0

    Replace 'relative->value' with

    'relative' ->> 'value'

    Same for period.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search