.NET Information center: Postgres 9.3 feature highlight: JSON data generation

Wednesday, April 17, 2013

Postgres 9.3 feature highlight: JSON data generation

Postgres 9.2 has introduced JSON as a server data type. At this point, the data was simply stored on server side with integrated wrappers checking that data had a correct JSON format. It was a good first step in order to store directly JSON data on server side but core features in 9.2 have its limitations in terms of JSON data manipulation and transformation.

Two new sets of JSON features have been added to PostgreSQL 9.3 planned to be released this year: functions related to data generation and a new set of APIs for data processing. The one this post deals with the ability to generate JSON data based on existing data types. The second set of features (operators and new processing functions) will be explained in a future post.

So… Functions for JSON data generation have been added by this commit.

commit 38fb4d978c5bfc377ef979e2595e3472744a3b05

Author: Andrew Dunstan

Date: Sun Mar 10 17:35:36 2013 -0400

JSON generation improvements.

This adds the following:

　json_agg(anyrecord) -> json

　to_json(any) -> json

　hstore_to_json(hstore) -> json (also used as a cast)

　hstore_to_json_loose(hstore) -> json

The last provides heuristic treatment of numbers and booleans.

Also, in json generation, if any non-builtin type has a cast to json,

that function is used instead of the type's output function.

Andrew Dunstan, reviewed by Steve Singer.

Catalog version bumped.

The first function called to_json permits to return a given value as valid JSON.

postgres=# create table aa (a bool, b text);

CREATE TABLE

postgres=# INSERT INTO aa VALUES (true, 'Hello "Darling"');

INSERT 0 1

postgres=# INSERT INTO aa VALUES (false, NULL);

INSERT 0 1

postgres=# SELECT to_json(a) AS bool_json, to_json(b) AS txt_json FROM aa;

bool_json | txt_json

-----------+---------------------

true | "Hello \"Darling\""

false |

(2 rows)

Boolean values are returned as plain true/false, texts are quoted as valid JSON fields.

json_agg is a function that can transform a record into a JSON array.

postgres=# SELECT json_agg(aa) FROM aa;

json_agg

---------------------------------------

[{"a":true,"b":"Hello \"Darling\""}, +

{"a":false,"b":null}]

(1 row)