Преобразование данных JSON в табличные данные в Snowflake
У меня есть необработанные данные JSON, которые вводятся в Snowflake через Kafka с устройств. Я пытаюсь преобразовать некоторые значения в таблицу с помощью динамической таблицы Snowflake (поэтому мне просто нужно исправить оператор SELECT).
Это снимок данных JSON, имя столбца — RECORD_CONTENT.
{
"enterprise": "xx",
"genericTrap": x,
"pduBerEncoded": "xxx",
"pduRawBytes": "xxxx",
"peerAddress": "xx",
"peerPort": "xx",
"securityName": "xxx",
"specificTrap": xx,
"sysUpTime": xxxx,
"variables": [
{
"oid": "column_a",
"type": "octetString",
"value": "xxx
},
{
"oid": "column_b",
"type": "integer",
"value": "xxx"
}
]
}
ожидаемый результат таков:
столбец_а | столбец_б | специфическая_ловушка
ххх | ххх | хх
Для других устройств, которым мне нужно только анализировать переменные, я смог выполнить этот базовый запрос:
SELECT
MAX(CASE WHEN f.value:oid::STRING = 'column_a' THEN f.value:value::STRING END) AS column_a,
MAX(CASE WHEN f.value:oid::STRING = 'column_b' THEN f.value:value::STRING END) AS column_b
FROM table_a,
LATERAL FLATTEN(input => RECORD_CONTENT:variables) f
GROUP BY
f.seq
Однако введение конкретного столбца ловушек оказалось для меня немного сложной задачей. Не могли бы вы мне помочь?
Заранее благодарю.
Обновлено: конкретная ловушка имеет только 2 значения: 1 или 2. Следовательно, группировка по конкретной ловушке невозможна.
Ответы 2
выбирать max(случай, когда rc.value:oid::string='column_a', затем rc.value:value::string end) columns_a ,max(случай, когда rc.value:oid::string='column_b', затем rc.value:value::string end) columns_b ,record_content:specificTrap::string как specific_trap
из json_data а, боковое сглаживание (input=>record_content:variables) rc группировать по spec_trap
Ваш ответ можно улучшить, добавив дополнительную вспомогательную информацию. Пожалуйста, отредактируйте , добавив дополнительную информацию, например цитаты или документацию, чтобы другие могли подтвердить правильность вашего ответа. Более подробную информацию о том, как писать хорошие ответы, вы можете найти в справочном центре.
Вероятно, вам придется решать одну задачу за раз. Сначала извлеките поля таблицы с переменными массива в строки, а затем транспонируйте их, например:
with table_a(col) as (
select
parse_json(
'{
"enterprise": "xx",
"genericTrap": "1",
"pduBerEncoded": "xxx",
"pduRawBytes": "xxxx",
"peerAddress": "xx",
"peerPort": "xx",
"securityName": "xxx",
"specificTrap": "1",
"sysUpTime": "xxxx",
"variables": [
{
"oid": "column_a",
"type": "octetString",
"value": "vala"
},
{
"oid": "column_b",
"type": "integer",
"value": "valb"
}
]
}'
) as variant
)
select
--seq,
any_value(specifictrap) specifictrap,
max(case oid when 'column_a' then oid_val else null end) column_a,
max(case oid when 'column_b' then oid_val else null end) column_b
from
(
select
f.seq seq,
col:specificTrap::VARCHAR specifictrap,
f.value:oid::VARCHAR oid,
f.value:value::VARCHAR oid_val
from
table_a,
lateral FLATTEN(input => table_a.col:variables::ARRAY) f
) t
group by
seq;
На первом этапе вы создаете промежуточную таблицу следующим образом:
Затем достаточно легко транспонировать таблицу, чтобы получить желаемый результат.
Невозможно сгруппировать по конкретной ловушке, поскольку конкретная ловушка имеет только значение 1 или 2. Таким образом, при группировке вместо этого будут возвращены только 2 строки.