Determining the type (array, object, or scalar) of a JSON value

Поиск
Список
Период
Сортировка
От Andrew Tipton
Тема Determining the type (array, object, or scalar) of a JSON value
Дата
Msg-id CA+M2pVWJHhx=+vWm=nsjT-SP7is+9dEa0OSN-voH82Gwb6mjnA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Determining the type (array, object, or scalar) of a JSON value  (Merlin Moncure <mmoncure@gmail.com>)
Список pgsql-general
Hi all,

I recently wanted to declare a CHECK constraint to ensure that a JSON value was an object with keys that were scalars (not nested objects or arrays).  This proved to be more difficult than I had expected.  In the end, I had to write a json_typeof() function in pl/pgsql.  It's a simple function but uses a rather horrifying technique:  it traps the exceptions raised when trying to apply json_array_length() to a non-array and json_each() to a non-object!  I couldn't figure out a robust technique to further determine the type of a scalar value (int, float, text, or boolean) so this function simply returns one of 'array', 'object', or 'scalar'.

  CREATE FUNCTION json_typeof(json_value JSON)
  RETURNS TEXT AS $$
  BEGIN
    BEGIN
      PERFORM json_array_length(json_value);
      RETURN 'array';
    EXCEPTION WHEN invalid_parameter_value THEN NULL;
    END;
    BEGIN
      PERFORM json_each(json_value);
      RETURN 'object';
    EXCEPTION WHEN invalid_parameter_value THEN NULL;
    END;
    RETURN 'scalar';
  END;
  $$ LANGUAGE plpgsql STRICT IMMUTABLE;

With this function, it's fairly simple to define a domain which only permits an array or an object as the top-level element:

  CREATE DOMAIN json_document CHECK (json_typeof(VALUE) IN ('array', 'object'));

I feel that the json_typeof function is worthy of being a built-in function.  Thoughts?


Cheers,
-Andrew

В списке pgsql-general по дате отправления:

Предыдущее
От: "Inoue, Hiroshi"
Дата:
Сообщение: Re: Segmentation fault with core dump
Следующее
От: Rebecca Clarke
Дата:
Сообщение: Get data type aliases