Как разобрать JSON в стандартном SQL BigQuery?
После потоковой передачи некоторых данных json в BQ у нас есть запись, которая выглядит так:
"{\"Type\": \"Some_type\", \"Identification\": {\"Name\": \"First Last\"}}"
Как мне извлечь из этого type? Например. Я хочу получить Some_type.
Перепробовал все возможные комбинации показанные в https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions безуспешно, а именно подумал:
SELECT JSON_EXTRACT_SCALAR(raw_json , "$[\"Type\"]") as parsed_type FROM `table` LIMIT 1000
это то, что мне нужно. Однако я получаю:
Недопустимый токен в JSONPath по адресу: ["Type"]
Изображение предварительного просмотра строк
Ответы 1
Ниже приведен пример для стандартного SQL BigQuery.
#standardSQL
WITH `project.dataset.table` AS (
SELECT 1 id, "{\"Type\": \"Some_type\", \"Identification\": {\"Name\": \"First Last\"}}" raw_json UNION ALL
SELECT 2, '{"Type": "Some_type", "Identification": {"Name": "First Last"}}'
)
SELECT id, JSON_EXTRACT_SCALAR(raw_json , "$.Type") AS parsed_type
FROM `project.dataset.table`
с результатом
Row id parsed_type
1 1 Some_type
2 2 Some_type
См. ниже пример обновления - взгляните на третью запись, которая, я думаю, имитирует ваш случай.
#standardSQL
WITH `project.dataset.table` AS (
SELECT 1 id, "{\"Type\": \"Some_type\", \"Identification\": {\"Name\": \"First Last\"}}" raw_json UNION ALL
SELECT 2, '''{"Type": "Some_type", "Identification": {"Name": "First Last"}}''' UNION ALL
SELECT 3, '''"{\"Type\": \"
null1\"}"
'''
)
SELECT id,
JSON_EXTRACT_SCALAR(REGEXP_REPLACE(raw_json, r'^"|"$', '') , "$.Type") AS parsed_type
FROM `project.dataset.table`
с результатом
Row id parsed_type
1 1 Some_type
2 2 Some_type
3 3 null1
Примечание: я использую null1 вместо null, чтобы вы могли легко увидеть, что это не NULL, а строка null1
можете ли вы показать пример нескольких строк в изображение с панелью результатов в режиме таблицы - чтобы мы видели, с какими именно данными мы имеем дело
Я добавил изображение, я думаю, мне нужно раскавычить его, прежде чем звонить JSON_EXTRACT_SCALAR
изображение, которое вы только что добавили, находится в предварительном просмотре JSON или предварительном просмотре таблицы?
Это предварительный просмотр таблицы, предварительный просмотр Json "raw_json": "\"{\\\"Type\\\": \\\"
Я знаю, это очень странно, но когда я вставляю данные, как в ответе, он работает и анализирует правильную информацию. Но когда те же данные являются столбцом, я возвращаю нули.