Overview

SereneDB supports SQL functions that are useful for reading values from existing JSON and creating new JSON data.

About JSON

JSON is an open standard file format and data interchange format that uses human-readable text to store and transmit data objects consisting of attribute–value pairs and arrays (or other serializable values). While it is not a very efficient format for tabular data, it is very commonly used, especially as a data interchange format.

JSONPath and JSON Pointer Syntax

SereneDB implements multiple interfaces for JSON extraction: JSONPath and JSON Pointer. Both of them work with the arrow operator (->) and the json_extract function call.

Note that SereneDB only supports lookups in JSONPath, i.e., extracting fields with .<key> or array elements with [<index>]. Arrays can be indexed from the back and both approaches support the wildcard *. SereneDB does not support the full JSONPath syntax because SQL is readily available for any further transformations.

Tip

It's best to pick either the JSONPath or the JSON Pointer syntax and use it in your entire application.

Indexing

Attention

Following PostgreSQL's conventions, SereneDB uses 1-based indexing for its ARRAY and LIST data types but 0-based indexing for the JSON data type.

Examples

Loading JSON

Read a JSON file from disk, auto-infer options:

Query

SELECT * FROM 'todos.json';

Result

 userid | id | title               | completed--------+----+---------------------+-----------      1 |  1 | write docs fixtures | f      1 |  2 | run sqllogictest    | t

Use the read_json function with custom options:

Query

SELECT *FROM read_json('todos.json',               format = 'array',               columns = {userId: 'UBIGINT',                          id: 'UBIGINT',                          title: 'VARCHAR',                          completed: 'BOOLEAN'});

Result

 userid | id | title               | completed--------+----+---------------------+-----------      1 |  1 | write docs fixtures | f      1 |  2 | run sqllogictest    | t

Read a JSON file from stdin, auto-infer options:

cat data/json/todos.json | serened shell -c "SELECT * FROM read_json('/dev/stdin')"

Read a JSON file into a table:

Query

CREATE TABLE todos (userId BIGINT, id BIGINT, title VARCHAR, completed BOOLEAN);
COPY todos FROM 'todos.json' (AUTO_DETECT true);

Alternatively, create a table without specifying the schema manually with a CREATE TABLE ... AS SELECT clause:

Query

CREATE TABLE todos AS    SELECT * FROM 'todos.json';

Writing JSON

Write the result of a query to a JSON file:

Query

COPY (SELECT * FROM todos) TO 'todos.json';

JSON Data Type

Create a table with a column for storing JSON data and insert data into it:

Query

CREATE TABLE example (j JSON);
INSERT INTO example VALUES    ('{ "family": "anatidae", "species": [ "duck", "goose", "swan", null ] }');

Retrieving JSON Data

Retrieve the family key's value as JSON with the subscript operator:

Query

SELECT j['family'] FROM example;

Result

 j------------ "anatidae"

Extract the family key's value as JSON with the -> operator:

Query

SELECT j->'family' FROM example;

Result

 ?column?------------ "anatidae"

Extract the family key's value as a VARCHAR with the ->> operator:

Query

SELECT j->>'family' FROM example;

Result

 ?column?---------- anatidae

Keys with Special Characters

JSON object keys that contain special characters such as [ and . can be accessed by passing the key name to the -> operator:

Query

SELECT '{"a[b].c":42}'->'a[b].c' AS v;

Result

 v---- 42