json_accessors.md

json_accessors

Installing

CREATE EXTENSION json_accessors;

Extension is compatible witgh PostgreSQL 9.1 and 9.2.

Description

PostgreSQL stored functions for accessing JSON fields.

This project contains PostgreSQL extension json_accessors with stored functions. Extension is native and writen in C on top of cJSON library.

PostgreSQL have had no JSON support until version 9.2, which introduced some support. These 9.2 functions won't help with indexing JSON data.

JSON parsing functions may be written using PL/V8 module, this article has an example of PL/V8 usage. Project provides accessor functions for JSON without using PL/V8.

Usage

Extension has two usage scenarios:

1. Extract a json object by 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;
   

2. Convert an JSON array to 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 JSON objects to PG array text[].

Example:

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

json_array_to_multi_array(text) -> text[]

Cnverts a JSON array of JSON arrays to PG array of text arrays text[].

Example:

			TODO fix a primer
    select json_array_to_multi_array('[["foo", "bar"], []]') = array[['foo','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 JSON objects to PG array text[].

Example:

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

json_get_multi_array(text, text) -> text[]

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

Example:

			TODO
    select json_get_multi_array('{"key" : [["foo", "bar"], []] }', 'key') = array[['foo','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 Apache License 2.0.