Пример ниже для стандартного SQL BigQuery
#standardSQL
CREATE TEMPORARY FUNCTION parseJson(y STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
var z = new Array();
processKey(JSON.parse(y), '');
function processKey(node, parent) {
Object.keys(node).map(function(key) {
value = node[key].toString();
if (value !== '[object Object]') {
if (parent !== '' && parent.substr(parent.length-1) !== '.') {
z.push(parent + '.' + key + ':' + value)
} else {
z.push(key + ':' + value)
}
} else {
if (parent !== '' && parent.substr(parent.length-1) !== '.') {parent += '.'};
processKey(node[key], parent + key);
};
});
};
return z
""";
WITH `my_table` AS (
SELECT 1 id, '{"sku_types":{"id":"5433306","product_code":"adfklj_ewkj"},"additional_info":"Face 30 ml","stock_level":"20+"}' AS json_string UNION ALL
SELECT 2, '{"additional_info":"Face 100 ml","offer_info":"30%"}' AS json_string
)
SELECT id,
ARRAY(
SELECT AS STRUCT SPLIT(kv, ':')[OFFSET(0)] key, SPLIT(kv, ':')[SAFE_OFFSET(1)] value
FROM UNNEST(parseJson(json_string)) kv
) params
FROM my_table
с результатом
Row id params.key params.value
1 1 sku_types.id 5433306
sku_types.product_code adfklj_ewkj
additional_info Face 30 ml
stock_level 20+
2 2 additional_info Face 100 ml
offer_info 30%
как вы можете видеть, вместо разбора всех возможных атрибутов в отдельные столбцы (что здесь совершенно невозможно - если вы не знаете их заранее) - вышеприведенный подход объединяет их в пары ключ: значение внутри массива параметров
Примечание. В приведенном выше примере я использую :
для создания пар ключ: значение, а затем разделяю их. Если вы ожидаете, что значения имеют этот символ - вы можете изменить код и вместо :
использовать что-то более уникальное, например :::::::
Быстрое обновление с учетом комментария:
... проблема в том, что некоторые значения json равны нулю, и в этом случае выдается ошибка
#standardSQL
CREATE TEMPORARY FUNCTION parseJson(y STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
var z = new Array();
processKey(JSON.parse(y), '');
function processKey(node, parent) {
Object.keys(node).map(function(key) {
if (!node[key]) {
value = 'n/a'
} else {
value = node[key].toString();
}
if (value !== '[object Object]') {
if (parent !== '' && parent.substr(parent.length-1) !== '.') {
z.push(parent + '.' + key + ':' + value)
} else {
z.push(key + ':' + value)
}
} else {
if (parent !== '' && parent.substr(parent.length-1) !== '.') {parent += '.'};
processKey(node[key], parent + key);
};
});
};
return z
""";
WITH `my_table` AS (
SELECT 1 id, '{"sku_types":{"id":"5433306","product_code":"adfklj_ewkj"},"additional_info":"Face 30 ml","stock_level":"20+"}' AS json_string UNION ALL
SELECT 2, '{"additional_info":"Face 100 ml","offer_info":"30%"}' AS json_string union all
SELECT 3 as id , '{"offer_info":"30%", "price":null}' AS json_string
)
SELECT id,
ARRAY(
SELECT AS STRUCT SPLIT(kv, ':')[OFFSET(0)] key, SPLIT(kv, ':')[SAFE_OFFSET(1)] value
FROM UNNEST(parseJson(json_string)) kv
) params
FROM my_table
с результатом
Row id params.key params.value
1 1 sku_types.id 5433306
sku_types.product_code adfklj_ewkj
additional_info Face 30 ml
stock_level 20+
2 2 additional_info Face 100 ml
offer_info 30%
3 3 offer_info 30%
price n/a
Как видите, я заменяю нули на 'n/a'
, но вы можете применить любую логику, какую захотите.
person
Mikhail Berlyant
schedule
30.08.2019