Work with JSON data¶

SKDB supports a JSON data type for columns. You can use SKDB to store JSON documents in a table:

CREATE TABLE json_docs (id TEXT PRIMARY KEY, doc JSON);
INSERT into json_docs values ('key', '{"foo": {"bar": "baz"}}');

For a walkthrough use case of using JSON with SKDB, please see this blog post.

Query JSON document content¶

SKDB allows you to write SQL queries against JSON documents. To do so you create a reactive view using json_extract() and then query the reactive view. As the underlying JSON documents change the view is automatically and incrementally maintained.

You can do everything with this reactive view that you normally would: create indices on top of it for fast querying, build further reactive views, watch queries for changes, etc.

Here is an example reactive view extracting fields from a document in to columns:

await skdb.exec(`
  CREATE REACTIVE VIEW products AS
    json_extract(
      json_docs,
      doc,
      '{
         id<string>,
         brand<string>,
         title<string>,
         category<string>,
         price<int>,
         rating<num>
       }'
  );
`);

`json_extract` pattern matching syntax¶

The pattern matching syntax for json_extract is designed to be expressive and flexible, catering to various data retrieval needs. Below are the key components of the syntax, illustrated with examples.

Basic extraction¶

Direct Field Extraction: Extracts the value of a specified field.
```
create reactive view my_view as
json_extract(my_table, my_column, '{field_name: colName}');
```
Example: {x: numbers} extracts the value of x into numbers.

Entries of the form {"x": 1} {"x": 2} are turned into an SQL table made of one column called numbers and two entries 1 and 2.

Field with special characters: if the fields contain special characters, use double quotes.

create reactive view my_view as
json_extract(my_table, my_column, '{"field name": var}');

Arrays¶

Array Extraction: To extract elements from an array, use [].
```
create reactive view array_view as
json_extract(my_table, my_column, '{array_field_name[]: var}');
```
Example: {x[]: var1} iterates over an array x, extracting each element into var1.
Nested Arrays: For nested arrays, use [][] or [index] for specific elements.
```
create reactive view nested_array_view as
json_extract(my_table, my_column, '{array_field_name[][]: var}');
```
Example: "{x[][]: var}" extracts elements from a nested array.

Optional fields¶

Optional Fields: Prefix with ? to indicate an optional field.
```
create reactive view optional_field_view as
json_extract(my_table, my_column, '{?"optional_field_name": var}');
```
Example: "{?y[0]: var2}" conditionally extracts the first element of y if it exists.

Type specifications¶

Type Casting: Specify the desired type with <type> after the variable name.
```
create reactive view typed_view as
json_extract(my_table, my_column, '{field_name: var<type>}');
```
Example: {field1<int>, field2<int>} matches field1 and field2 only when they are integers.

N.B: The name of captured columns can be ommitted when it is the same as the field name. In this example: {field1<int>, field2<int>} is equivalent to {field1: field1<int>, field2: field2<int>}

Wildcards and filters¶

Wildcard Matches: Use % to match any field path.
```
create reactive view wildcard_view as
json_extract(my_table, my_column, '{%: var}');
```
Example: {%: var} matches any path, extracting its value into var.
Specific Pattern Matching: Combine wildcards and specific field names for complex patterns.
```
create reactive view pattern_view as
json_extract(my_table, my_column, '%.email: var');
```
Example: %.email: var extracts the value of the field email from any path.

Advanced patterns¶

Nested Object Extraction: To extract values from nested objects, use the dot notation.
```
create reactive view nested_object_view as
json_extract(my_table, my_column, '{parent_field.child_field: var}');
```
Example: {data[].id: var<int>} extracts id as an integer from each object in the data array.
Combining Patterns: Patterns can be combined to extract multiple values or to handle complex data structures.
```
create reactive view combined_pattern_view as
json_extract(my_table, my_column, '{field1: var1, field2[]: var2}');
```
Example: {x[]: v1, y[]: v2} extracts elements from both x and y arrays, correlating each x with every y.