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
|
Список | 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;
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
-Andrew
В списке pgsql-general по дате отправления: