Postgres split json into columns

To split JSON into columns in PostgreSQL, we can use the JSON functions available in the PostgreSQL JSON module.

Assuming we have a table called “my_table” with a JSON column called “data”, and the JSON data looks like this:

    
        {
            "name": "John Doe",
            "age": 30,
            "address": {
                "street": "123 Main St",
                "city": "New York",
                "country": "USA"
            }
        }
    
    

We can extract specific JSON fields or keys into separate columns in a query:

    
        SELECT
            data -> 'name' AS name,
            data -> 'age' AS age,
            data -> 'address' -> 'street' AS street,
            data -> 'address' -> 'city' AS city,
            data -> 'address' -> 'country' AS country
        FROM
            my_table;
    
    

In the above query, we use the JSON arrow operator “->” to navigate through the JSON structure and access specific fields. We alias each extracted field with a column name.

The result of the query will give us the desired JSON fields split into separate columns: