Поиск по вложенным JSON в Bigquery

Question

Поиск по вложенным JSON в Bigquery

Рейтинг: 0Ответов: 1Опубликовано: 13.01.2023

Всем привет!

Существует таблица task_table вида (см. ниже). Задача заключается в том, чтобы для каждой организации (в столбце Object_name) извлечь ряд параметров (дни недели (BOOL) и часы открытия/закрытия) из вложенного JSON в атрибуте Nested_data.

Структура вложенного JSON выглядит следующим образом:

data : {
    object1 : [ 2 items ]
    object2 : {1 prop}
    subtopic : {
        unique_identifier : {
            created_time : ‘xxxx-xx-xxT00:00:000Z”
            display_options : {3 props}
            subobject1 : {3 props}
            sections : [
                created_time : ‘xxxx-xx-xxT00:00:000Z”
                external_notes : value
                title1 : NULL
                title2 : NULL
                working_hours : [
                    days_array : [
                        0 : TRUE
                        1 : FALSE
                        2 : TRUE
                        3 : FALSE
                        4 : TRUE
                        5 : FALSE
                        6 : FALSE
                        ]
                    end_time : ’23:59:00’
                    start_time : ’00:00:00’ 
                    }}}

Используя различные комбинации JSON_EXTRACT() и UNNEST() мне удалось извлечь дни работы и часы открытия/закрытия. Код ниже:

WITH pre_table AS (
SELECT t.object_name as object_name,
json_extract_array(t.Nested_data, '$.data.subtopic.G236_28061TQS.sections.') as js --'G236_28061TQS' это unique_identifier
FROM task_table t
WHERE t.Object_id = '730958720-Q23U')
SELECT pre_table.object_name as object_name,
JSON_EXTRACT(ed_js, '$.working_hours[0].days_array[0]') as Monday,
JSON_EXTRACT(ed_js, '$.working_hours[0].days_array[1]') as Tuesday,
JSON_EXTRACT(ed_js, '$.working_hours[0].days_array[2]') as Wednesday,
JSON_EXTRACT(ed_js, '$.working_hours[0].days_array[3]') as Thursday,
JSON_EXTRACT(ed_js, '$.working_hours[0].days_array[4]') as Friday,
JSON_EXTRACT(ed_js, '$.working_hours[0].days_array[5]') as Saturday,
JSON_EXTRACT(ed_js, '$.working_hours[0].days_array[6]') as Sunday,
JSON_EXTRACT(ed_js, '$.working_hours[0].startTime') as start_time,
JSON_EXTRACT(ed_js, '$.working_hours[0].endTime') as end_time,
FROM pre_table
LEFT JOIN UNNEST(pre_table.js) as ed_js;

Результат:

Для одной организации я получил, что хотел, однако для всех организаций не могу повторить подобное, так как вложенные JSONы хоть и имеют одинаковую структуру, но unique_identifier - уникальный ключ (буквенно цифровой код) и для каждого JSON в запросе нужно прописывать свой ключ в $Path.

Соответственно я хочу понять, каким образом можно извлечь данные о днях и часах работы для всех организаций?

Насколько я понял какого-то фильтра по вложенным ключам нет. Также пробовал скрипт из этой ветки: https://stackoverflow.com/questions/51673083/jsonpath-in-bigquery-doesnt-support-for-filter-suggestions-for-alternatives, в результате выдает NULL.

Если подставлять * как заполнитель в Path, BigQuery выдает ошибку: Unsupported operator in JSONPath: *

Заранее спасибо!

sql json bigquery

Источник: Stack Overflow на русском

Answer 1

▲ 1Принят

Я думаю проще всего JavaScript UDF использовать, там можно что угодно с JSON делать. Скажем,

CREATE TEMP FUNCTION get_any_field(j JSON)
RETURNS JSON
LANGUAGE js
AS r"""
  return Object.values(j)[0];
""";

Я вот на таком протестировал (в следующий раз просьба - выкладывать пример валидного JSON), использовал тип JSON - с ним проще работать, чем со старыми функциями:

declare json_str string;

set json_str = """
{
  "name": "foo",
    "data": {
        "object1": ["item1", "item2"],
        "object2": {
            "prop_name": "prop_value"
        },
        "subtopic": {
            "unique_id123": {
                "created_time": "xxxx - xx - xxT00: 00: 000 Z",
                "display_options": {
                    "prop_name1": "prop_value",
                    "prop_name2": "prop_value",
                    "prop_name3": "prop_value"
                },
                "subobject1": {
                    "prop_name1": "prop_value",
                    "prop_name2": "prop_value",
                    "prop_name3": "prop_value"
                },
                "sections": [
                {
                    "created_time": "xxxx - xx - xxT00: 00: 000 Z",
                    "external_notes": "value",
                    "working_hours": {
                      "days_array": [
                        true, false, true, false, true, false, false
                      ]
                    }
                }
                ]
            }
        }
    }
}
""";

CREATE TEMP FUNCTION get_any_field(j JSON)
RETURNS JSON
LANGUAGE js
AS r"""
  return Object.values(j)[0];
""";

WITH table AS (
  select json_str
),
json_table AS (
  select 
    parse_json(json_str) as js
  from 
    table
),
sections_table AS (
  select
    js.name,
    JSON_QUERY_ARRAY(get_any_field(js.data.subtopic).sections) as sections
  from 
    json_table
)
select 
  name, s2.working_hours.days_array as working_days
from 
  sections_table as j2,
  j2.sections as s2

Получилось

name    working_days
"foo"   [true,false,true,false,true,false,false]

Поиск по вложенным JSON в Bigquery

Ответы