Skip to main content
SereneDBSereneDB
897Contact Us

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 json_extract function call: json_extract(j, '$.a.b') and json_extract(j, '/a/b').

The arrow operators follow PostgreSQL instead. -> and ->> take a key or an array index (j -> 'a', j -> 0), and #> and #>> take a path array (j #> '{a,b}'). A JSONPath or JSON Pointer string after -> matches no key and returns NULL.

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.

Indexing​

Examples​

Loading JSON​

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

SQL
SELECT * FROM 'todos.json';
 userId | id | title               | completed
--------+----+---------------------+-----------
      1 |  1 | write docs fixtures | f
      1 |  2 | run sqllogictest    | t

Use the read_json function with custom options:

SQL
SELECT *FROM read_json('todos.json',               format = 'array',               columns = {userId: 'UBIGINT',                          id: 'UBIGINT',                          title: 'VARCHAR',                          completed: 'BOOLEAN'});
 userid | id | title               | completed
--------+----+---------------------+-----------
      1 |  1 | write docs fixtures | f
      1 |  2 | run sqllogictest    | t

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

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

Read a JSON file into a table:

SQL
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:

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

Writing JSON​

Write the result of a query to a JSON file:

SQL
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:

SQL
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:

SQL
SELECT j['family'] FROM example;
 j
------------
 "anatidae"

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

SQL
SELECT j->'family' FROM example;
 ?column?
------------
 "anatidae"

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

SQL
SELECT j->>'family' FROM example;
 ?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:

SQL
SELECT '{"a[b].c":42}'->'a[b].c' AS v;
 v
----
 42
Edit this page ↗