SELECT query for BigQuery with flattened array objects

Given a bigquery table called Person that has the following 3 records. How do I write query to extract output in the following format?

Input:

{
    "birthDate": "2024-01-01",
    "name":[
        {
            "given": "John",
            "family": "Wayne"
        }
    ],
    "link":[
        {
            "target": {
              "patientId": "patient12345",
              "personId": null,
              "type": "Patient"
            }
        },
        {
            "target": {
              "patientId": null,
              "personId": "personABCD",
              "type": "Person"
            }
        }
    ]
},
{
    "birthDate": "2023-01-01",
    "name":[
        {
            "given": "Jane",
            "family": "Wayne"
        }
    ],
    "link":[
        {
            "target": {
              "patientId": "patient45345",
              "personId": null,
              "type": "Patient"
            }
        },
        {
            "target": {
              "patientId": null,
              "personId": "personXCFD",
              "type": "Person"
            }
        }
    ]
},
{
    "birthDate": "2022-01-01",
    "name":[
        {
            "given": "Jill",
            "family": "Wayne"
        }
    ],
    "link":[
        {
            "target": {
              "patientId": null,
              "personId": "personFWWA",
              "type": "Person"
            }
        }
    ]
},
{
    "birthDate": "2021-01-01",
    "name":[
        {
            "given": "Jack",
            "family": "Wayne"
        }
    ],
    "link":[
        {
            "target": {
              "patientId": "patient0942",
              "personId": null,
              "type": "Patient"
            }
        }
    ]
},
{
    "birthDate": "2020-01-01",
    "name":[
        {
            "given": "Jim",
            "family": "Wayne"
        }
    ],
    "link":[
    ]
}

Output:

given, family, patientId, personId
----------------------------------
John, Wayne, patient12345, personABCD
Jane, Wayne, patient45345, personXCFD
Jill, Wayne, null, personFWWA
Jack, Wayne, patient0942, null
Jim, Wayne, null, null

Given a bigquery table called Person that has the following 3 records. How do I write query to extract output in the following format?

Input:

{
    "birthDate": "2024-01-01",
    "name":[
        {
            "given": "John",
            "family": "Wayne"
        }
    ],
    "link":[
        {
            "target": {
              "patientId": "patient12345",
              "personId": null,
              "type": "Patient"
            }
        },
        {
            "target": {
              "patientId": null,
              "personId": "personABCD",
              "type": "Person"
            }
        }
    ]
},
{
    "birthDate": "2023-01-01",
    "name":[
        {
            "given": "Jane",
            "family": "Wayne"
        }
    ],
    "link":[
        {
            "target": {
              "patientId": "patient45345",
              "personId": null,
              "type": "Patient"
            }
        },
        {
            "target": {
              "patientId": null,
              "personId": "personXCFD",
              "type": "Person"
            }
        }
    ]
},
{
    "birthDate": "2022-01-01",
    "name":[
        {
            "given": "Jill",
            "family": "Wayne"
        }
    ],
    "link":[
        {
            "target": {
              "patientId": null,
              "personId": "personFWWA",
              "type": "Person"
            }
        }
    ]
},
{
    "birthDate": "2021-01-01",
    "name":[
        {
            "given": "Jack",
            "family": "Wayne"
        }
    ],
    "link":[
        {
            "target": {
              "patientId": "patient0942",
              "personId": null,
              "type": "Patient"
            }
        }
    ]
},
{
    "birthDate": "2020-01-01",
    "name":[
        {
            "given": "Jim",
            "family": "Wayne"
        }
    ],
    "link":[
    ]
}

Output:

given, family, patientId, personId
----------------------------------
John, Wayne, patient12345, personABCD
Jane, Wayne, patient45345, personXCFD
Jill, Wayne, null, personFWWA
Jack, Wayne, patient0942, null
Jim, Wayne, null, null

Share Improve this question asked Mar 18 at 19:33 RKodakandla 3,50215 gold badges64 silver badges87 bronze badges

Add a comment |

1 Answer 1

Sorted by: Reset to default 0

You can try the below query. This leverages UNNEST that expands the arrays and then ARRAY_TO_STRING to concatenate the values within each array into a single comma-separated string. This effectively "flattens" the nested structure into a table-like format that you intend.

SELECT
    ARRAY_TO_STRING(ARRAY(
        SELECT name.given FROM UNNEST(Person.name) AS name
    ), ','),
    ARRAY_TO_STRING(ARRAY(
        SELECT name.family FROM UNNEST(Person.name) AS name
    ), ','),
    ARRAY_TO_STRING(ARRAY(
        SELECT link.target.patientId FROM UNNEST(Person.link) AS link
    ), ','),
    ARRAY_TO_STRING(ARRAY(
        SELECT link.target.personId FROM UNNEST(Person.link) AS link
    ), ',')
  FROM
    `your-project.your-dataset.Person` AS Person

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

SELECT query for BigQuery with flattened array objects - Stack Overflow

1 Answer 1

与本文相关的文章

评论列表(0)