Как я могу получить тип значения JSON в Presto?

Question

У меня есть строка JSON, которую я запрашиваю с помощью Presto, и я хочу агрегировать по типам значений. Для этого мне нужно получить тип значения. В частности, для JSON вроде:

      {
  "a": 1,
  "b": "a",
  "c": true,
  "d": [ 1 ],
  "e": { "f": "g" },
}

Я хотел бы получить значение на $.a является целым числом, значение в $.b является строкой и т. д. (информация не должна быть вложенной, поэтому было бы достаточно знать, что $.d это массив и $.e это объект).

typeof похоже, возвращает только varchar или json, в зависимости от того, как вы извлекаете JSON из строки:

      SELECT
    typeof(json_extract(j, '$.a')),
    typeof(json_extract_scalar(j, '$.a'))
FROM (SELECT '{"a":1,"b":"a","c":true,"d":[1]}' AS j);

дает мне:

      _col0   _col1
json    varchar(32)

Как определить тип значения JSON для одного из этих полей?

Answer 1

• json_extract получает значение в строке JSON в качестве типа.
• json_format берет json значение и преобразует его обратно в строку JSON.

Я думаю, вы можете взломать эти две вещи вместе, чтобы получить тип значения в позиции, исследуя отформатированную строку.

      CASE
    WHEN substr(value, 1, 1) = '{'  THEN 'object'
    WHEN substr(value, 1, 1) = '['  THEN 'array'
    WHEN substr(value, 1, 1) = '"'  THEN 'string'
    WHEN substr(value, 1, 1) = 'n'  THEN 'null'
    WHEN substr(value, 1, 1) = 't'  THEN 'boolean'
    WHEN substr(value, 1, 1) = 'f'  THEN 'boolean'
    ELSE 'number'
END AS t

(где значение json_format(json_extract(json_string, '$.your_json_path')))

Вот пример:

      SELECT obj, value,
   CASE
       WHEN substr(value, 1, 1) = '{'  THEN 'object'
       WHEN substr(value, 1, 1) = '['  THEN 'array'
       WHEN substr(value, 1, 1) = '"'  THEN 'string'
       WHEN substr(value, 1, 1) = 'n'  THEN 'null'
       WHEN substr(value, 1, 1) = 't'  THEN 'boolean'
       WHEN substr(value, 1, 1) = 'f'  THEN 'boolean'
       ELSE 'number'
   END AS t
FROM (
    SELECT obj, json_format(json_extract(obj, '$.a')) AS value
    FROM (
        VALUES
          ('{"a":1}'),
          ('{"a":2.5}'),
          ('{"a":"abc"}'),
          ('{"a":true}'),
          ('{"a":false}'),
          ('{"a":null}'),
          ('{"a":[1]}'),
          ('{"a":{"h":"w"}}')
    ) AS t (obj)
)

Что дает такой результат:

      obj                  value            t
{"a":1}              1                number
{"a":2.5}            2.5              number
{"a":"abc"}          "abc"            string
{"a":true}           true             boolean
{"a":false}          false            boolean
{"a":null}           null             null
{"a":[1]}            [1]              array
{"a":{"h":"w"}}      {"h":"w"}        object