json_accessors extension

Installing

CREATE EXTENSION json_accessors;

Extension is compatible witgh PostgreSQL 9.1 and 9.2.

Description

Extension provides stored functions for accessing JSON fields by keys and converting JSON arrays.

Usage

Extension allows to extract a typed JSON object (array, text or object) by the string key:

    select json_get_text('{"create_date":"2009-12-01 01:23:45","key":"foobar"}', 'key') = 'foobar';
    select json_get_int('{"bar": 42 }', 'key') = 42;

Extension allows to convert a JSON array to a SQL array:

    select json_array_to_text_array('["foo", "bar", "baz"]') = array['foo','bar','baz'];

Functions can be also used for:

  • creating queries to JSON object fields
  • creating B-tree (default) indexes on JSON object fields
  • creating GIN indexes on JSON arrays

Interface

All functions takes a JSON string as a first argument. If a function should extract an object by the key, second argument is a key. Functions usually fails when actual object is not of requested type.

Scalar extraction functions

json_get_object(text, text) -> text

Extracts a child JSON object as a string.

Example:

    select json_get_text('{"foo": 42, "key":"foobar"]}', 'key') = 'foobar';

json_get_text(text, text) -> text

Extracts and returns a child JSON text object converted to PG text.

Example:

    select json_get_text('{"key":"foobar"]}', 'key') = 'foobar';

json_get_boolean(text, text) -> boolean

Extracts and returns a child JSON boolean object converted to PG boolean.

Example:

    select json_get_boolean('{"key": true]}', 'key');

json_get_int(text, text) -> int

Extracts and returns a child JSON integer object converted to PG int (int4).

Example:

    select json_get_int('{"key": 42]}', 'key') = 42;

json_get_bigint(text, text) -> bigint

Extracts and returns a child JSON integer object converted to PG bigint (int8).

Example:

    select json_get_bigint('{"key": 42]}', 'key') = 42;

json_get_numeric(text, text) -> numeric

Extracts and returns a child JSON integer object converted to PG numeric.

Example:

    select json_get_numeric('{"key": 42.99]}', 'key') = 42.99;

json_get_timestamp(text, text) -> timestamp

Extracts and returns a child JSON text object converted to PG timestamp without timezone. Timestamp format YYYY-MM-DD HH:MI:SS is fixed.

Example:

    select json_get_timestamp('{"foo":"qq", "bar": "2009-12-01 01:23:45"}', 'bar') = timestamp('2009-12-01 01:23:45');

Array extractor functions

Functions convert JSON arrays to PostgreSQL arrays.

json_array_to_object_array(text) -> text[]

Converts a JSON array of any JSON objects to PG array text[]. Each object is represented as a string.

Example:

    select json_array_to_object_array('[{"foo":42}, {"bar":[]}]') = array['{"foo":42}','{"bar":[]}']

json_array_to_text_array(text) -> text[]

Converts a JSON array of text objects to PG array text[].

Example:

    select json_array_to_text_array('["foo", "bar"]') = array['foo','bar'];

json_array_to_boolean_array(text) -> boolean[]

Converts a JSON array of boolean objects to PG array boolean[].

json_array_to_int_array(text) -> int[]

Converts a JSON array of integer objects to PG array int[] (int4[])

json_array_to_bigint_array(text) -> bigint[]

Converts a JSON array of integer objects to PG array bigint[] (int8[])

json_array_to_numeric_array(text) -> numeric[]

Converts a JSON array of integer objects to PG array numeric[].

json_array_to_timestamp_array(text) -> timestamp without time zone[]

Converts a JSON array of text objects to PG array timestamp[] without time zones. Time format is the same.

Indirect array exractor functions

Shortcut functions to directly extract an array by the key. Could be emulated by json_get_object and array convertor functions. Array is referenced in a JSON expression by a key.

json_get_object_array(text, text) -> text[]

Extract and converts a JSON array of any JSON objects to PG array text[]. JSON objects are represented as a text.

Example:

    select json_get_object_array('{"key" : [{"foo":42}, {"bar":[]}]}', 'key') = array['{"foo":42}','{"bar":[]}'];

json_get_text_array(text, text) -> text[]

Extract and converts a JSON array of text objects to PG array text[].

Example:

    select json_get_text_array('{"foo":"qq", "bar": ["baz1", "baz2", "baz3"]}', 'bar') = array['baz1','baz2','baz3'];

json_get_boolean_array(text, text) -> boolean[]

Extract and converts a JSON array of boolean objects to PG array boolean[].

json_get_int_array(text, text) -> int[]

Extract and converts a JSON array of integer objects to PG array int[] (int4[])

json_get_bigint_array(text, text) -> bigint[]

Extract and converts a JSON array of integer objects to PG array bigint[] (int8[])

json_get_numeric_array(text, text) -> numeric[]

Extract and converts a JSON array of integer objects to PG array numeric[].

json_get_timestamp_array(text, text) -> timestamp without time zone[]

Extract and converts a JSON array of text objects to PG array timestamp[] without time zones. Time format is the same.

Limitations

PostgreSQL numeric data type is parsed by a fixed position pattern and could be trimmed from a very big value.

Author

Copyright (c) 2012, Con Certeza LLC. All Right Reserved.

Developed by Eugene Seliverstov

Copyright and License

You can use any code from this project under the terms of PostgreSQL License.

Please consult with the COPYING for license information.