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?

Does this answer your question? How to get a json object as column in postgresql? — nbk
– nbk, Commented Jun 13, 2023 at 16:42
etc is not valid SQL, Please post a minimal reproducible example — Luuk
– Luuk, Commented Jun 13, 2023 at 17:22

Venkat · Accepted Answer · 2023-06-13 20:01:56Z

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;

edited Jun 13, 2023 at 20:01

answered Jun 13, 2023 at 17:22

Venkat

5712 silver badges6 bronze badges

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

2 Comments

Luuk Over a year ago

It only returns null, see: dbfiddle.uk/avZUe9UL

Venkat Over a year ago

try the updated query for the answer editing, see dbfiddle.uk/KCimdzFR

Collectives™ on Stack Overflow

JSON column to columns in SQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related