JSON column to columns in SQL

Question

I have one dataset where one column is in JSON format. I want to transform it to several columns in SQL. I am using Postgres in DBeaver

The JSON column is as below. I want to put everything that is inside abcde3 in several columns

{
   "abcde1":"INFO",
   "abcde2":"MOTOR",
   "abcde3":{
      "COLOR":"WHITE",
      "DATE_BIRTH":"05/09/1992",
      "GENDER":"F",
      etc
   },
   "PARTNER_ID":"XYZOEKF",
   "NAME":"ANYTHING",
   "userId":"1204923"
}

I already tried the following code in SQL but it didn't work

with jsonvalue as (
 select '{json_column_in_dataset}'::jsonb as jsonvalues
 FROM my_table
 WHERE any_condition = true
)

select 
        jsonvalues -> 'COLOR',
        jsonvalues -> 'DATE_BIRTH',
        etc
                    from jsonvalue

How can I transform it in SQL?

Answer 1

Change 'json_column_in_dataset' to the name of the JSON column in your table, and'my_table' to the name of your table.

WITH 
  abc AS (
    SELECT '{
      "abcde1": "INFO",
      "abcde2": "MOTOR",
      "abcde3": {
        "COLOR": "WHITE",
        "DATE_BIRTH": "05/09/1992",
        "GENDER": "F"
      },
      "PARTNER_ID": "XYZOEKF",
      "NAME": "ANYTHING",
      "userId": "1204923"
    }'::jsonb AS jsonvalues
  ),
  jsonvalue AS (
    SELECT
      jsonvalues::jsonb AS jsonvalues
    FROM
      abc
    -- WHERE
    --    any_condition = true
  )
SELECT
   jsonvalues -> 'abcde3' ->> 'COLOR' AS color,
   jsonvalues -> 'abcde3' ->> 'DATE_BIRTH' AS date_of_birth,
   jsonvalues -> 'abcde3' ->> 'GENDER' AS gender
   -- Add more columns as needed
FROM
   jsonvalue;