Importing from JSON

SereneDB supports the json data type with PostgreSQL-compatible operators for querying JSON documents.

Load JSON data

Prepare a JSON Lines file where each line is a valid JSON object. For example, stars.json:

{"name": "Cillian Murphy", "gender": "M", "birthdate": "1976-05-25"}
{"name": "Emily Blunt", "gender": "F", "birthdate": "1983-02-23"}
{"name": "Michelle Yeoh", "gender": "F", "birthdate": "1962-08-06"}

Create a table with a json column and load the file:

CREATE TABLE stars_json (star JSON);
COPY stars_json FROM '/path/to/stars.json';

Query JSON documents

Use the ->> operator to extract values as text:

SELECT star ->> 'name' AS name
FROM stars_json
WHERE star ->> 'gender' = 'F';

 name
---------------
 Emily Blunt
 Michelle Yeoh

Use the -> operator to extract values as JSON (preserving type):

SELECT star -> 'name' AS name
FROM stars_json;

 name
-----------------
 "Cillian Murphy"
 "Emily Blunt"
 "Michelle Yeoh"

Path access

Use #> and #>> to access nested values by path:

SELECT '{"a": {"b": ["foo", "bar"]}}'::JSON #>> ARRAY['a', 'b', '1'];

 ?column?
----------
 bar

Or use json_extract_path_text:

SELECT json_extract_path_text('{"a": {"b": [1, 2, 3]}}'::JSON, 'a', 'b', '0');

 json_extract_path_text
------------------------
 1

Convert JSON to structured tables

For better query performance, convert JSON documents into typed columns. This lets SereneDB use columnar storage and indexing:

CREATE TABLE stars (
    id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT,
    gender CHAR,
    birthdate DATE
);

INSERT INTO stars(name, gender, birthdate)
    SELECT star ->> 'name',
           star ->> 'gender',
           (star ->> 'birthdate')::DATE
    FROM stars_json;

Now you can query with full SQL and create search indexes on the structured data:

SELECT name, birthdate
FROM stars
WHERE birthdate > '1980-01-01'
ORDER BY birthdate;