Извлечь все ключи JSON

У меня есть столбец JSON j, например:

{'a': 2, 'b': {'b1': 3, 'b2': 5}}
{'c': 3, 'a': 5}
{'d': 1, 'c': 7}

Как я могу получить от Presto все отдельные (высокоуровневые) имена ключей? Т.е. Мне что-то нравится

select distinct foo(j)

Возвращать

['a', 'b', 'c', 'd']

(обратите внимание, что в этом случае я не слишком озабочен вложенными ключами)

Документация Presto не имеет функции, которая явно соответствует требованиям. Единственное, что выглядит близко, - это упоминание синтаксиса JSONPath, но даже это кажется неточным. По крайней мере одно из следующего должно возвращать что-то, но у меня все не удалось в Presto:

select json_extract(j, '$.*')
select json_extract(j, '$..*')
select json_extract(j, '$[*]')
select json_extract(j, '*')
select json_extract(j, '..*')
select json_extract(j, '$*.*')

Кроме того, я подозреваю, что это вернет значения, а не ключи из j (т.е. [2, 3, 5, 3, 5, 1, 7]).


json sql presto
person MichaelChirico    schedule 01.12.2017    source источник

Ответы (1)


arrow_upward
2
arrow_downward

Вы можете

  1. извлекать ключи верхнего уровня JSON с помощью map_keys(cast(json_column as map<varchar,json>))
  2. позже «сгладьте» коллекции ключей с помощью CROSS JOIN UNNEST
  3. тогда вы можете SELECT DISTINCT получить отдельные ключи верхнего уровня.

Пример объединения этого:

presto> SELECT DISTINCT m.key
     -> FROM (VALUES JSON '{"a": 2, "b": {"b1": 3, "b2": 5}}', JSON '{"c": 3, "a": 5}')
     ->     example_table(json_column)
     -> CROSS JOIN UNNEST (map_keys(CAST(json_column AS map<varchar,json>))) AS m(key);
 key
-----
 a
 b
 c
(3 rows)
person Piotr Findeisen    schedule 10.12.2017
comment
Дополнительное условие, что если ваш столбец хранится как varchar, например '{"a": 2, "b": {"b1": 3, "b2": 5}}', вы захотите преобразовать его в JSON с помощью json_parse(json_column), не cast(json_column as JSON) - person MichaelChirico; 14.12.2017
comment
Есть ли способ сделать это без CROSS JOIN? В БД с ›1 миллиардом строк это очень медленно. - person MichaelChirico; 14.12.2017
comment
Мой подход сейчас таков: увеличьте масштаб до достаточно небольшого подмножества данных, где я могу выполнить (что-то вроде 7 миллионов строк, где я нашел 36 уникальных ключей); затем добавьте предложение where not key in (keys_found_from_narrow_query) в более широкий запрос. 1) действительно ли это лучший подход к поиску уникальных ключей во всем наборе данных 2) есть ли лучший / менее итеративный способ реализовать это в масштабе? - person MichaelChirico; 14.12.2017