-2

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?

Rods2292
  • 665
  • 2
  • 10
  • 28
  • 1
    Does this answer your question? [How to get a json object as column in postgresql?](https://stackoverflow.com/questions/39945308/how-to-get-a-json-object-as-column-in-postgresql) – nbk Jun 13 '23 at 16:42
  • `etc` is not valid SQL, Please post a [mre] – Luuk Jun 13 '23 at 17:22

1 Answers1

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;
Venkat
  • 549
  • 6