Transpose a array that is in a json file into columns

Question

I have a JSON where in between is an array. I already extracted all the other information in the json but i cant do it with the array in it. IT looks like this in BigQuery and it is a String JSON

{
    "description":"Eminem.",
    "eDate":{
        "_seconds":1673668800,"_nanoseconds":409000000
    },
    "pId":"test-plan-1",
    "Id":"test-p-1",
    "startDate":{
        "_seconds":1673636400,"_nanoseconds":957000000
    },
    "Categories":[
        {
            "description":"Eminem 123.",
            "id":"cheap",
            "name":"Cheap Ticket", "sEnd":{"_seconds":1767283200,"_nanoseconds":225000000},
            "sStart":{"_seconds":1673272800,"_nanoseconds":330000000},
            "tRate":0.19,
            "uPrice":1.5
        }
    ],
    "title":"Apple",
    "vId":"test-v-1"
}

The array starts by categories and end by uPrice

Im expecting that all keys with their values have their own column . The data from JSON and from the array

Jiho Choi · Accepted Answer · 2023-01-19 13:13:03Z

One possible way would be using JSON_QUERY_ARRAY with ARRAY_TO_STRING. JSON_QUERY_ARRAY returns ARRAY<STRING> which cannot be directly parsed again.

Below is the result of the following query.

WITH
json_dataset AS (
    SELECT '{"description":"Eminem.","eDate":{"_seconds":1673668800,"_nanoseconds":409000000},"pId":"test-plan-1","Id":"test-p-1","startDate":{"_seconds":1673636400,"_nanoseconds":957000000},"Categories":[{"description":"Eminem 123.","id":"cheap","name":"Cheap Ticket", "sEnd":{"_seconds":1767283200,"_nanoseconds":225000000},"sStart":{"_seconds":1673272800,"_nanoseconds":330000000},"tRate":0.19,"uPrice":1.5}],"title":"Apple","vId":"test-v-1"}' as json_string
)
SELECT
    json_string,
    JSON_VALUE(
        json_string,'$.description'
    ) AS json_value_col,
    JSON_QUERY_ARRAY(
        json_string,'$.Categories'
    ) AS json_query_array_col,
    ARRAY_TO_STRING(
        JSON_QUERY_ARRAY(
            json_string,'$.Categories'
        ),
        ""
    ) AS json_array_string,
    JSON_VALUE(
        ARRAY_TO_STRING(JSON_QUERY_ARRAY(json_string,'$.Categories'),""),'$.description'
    ) AS target_example,
FROM json_dataset
;

Collectives™ on Stack Overflow

Transpose a array that is in a json file into columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related