BigQuery concat nested array json

Question

I have data that looks like

{
    "Attributes": [
        {
            "values": [
                {
                    "value": "20003"
                },
                {
                    "value": "30075"
                },
                {
                    "value": "40060"
                }
            ],
            "name": "price"
        }
    ],
    "attr2" : "val"
}

The output I want is concat all the values in the nested json array

price, "20003, 30075, 40060"

I tried some queries but failed to get the correct output.

Sergey Geron · Accepted Answer · 2020-11-26 06:46:28Z

You can use JSON_EXTRACT_ARRAY and ARRAY_TO_STRING:

WITH test_json AS (
  SELECT
    '''{
        "Attributes": [
            {
                "values": [
                    {
                        "value": "20003"
                    },
                    {
                        "value": "30075"
                    },
                    {
                        "value": "40060"
                    }
                ],
                "name": "price"
            }
        ],
        "attr2" : "val"
    }''' AS json_string
),
values_concatenated AS (
  SELECT ARRAY_TO_STRING(
    ARRAY(
      SELECT JSON_VALUE(json_values, '$.value')
      FROM UNNEST((SELECT JSON_EXTRACT_ARRAY(json_string, '$.Attributes[0].values') AS json_values FROM test_json)) as json_values
    ),
    ', '
  ) as values
)
SELECT
  (select json_value(json_string, '$.Attributes[0].name') from test_json),
  (select values from values_concatenated)

Collectives™ on Stack Overflow

BigQuery concat nested array json

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