Skip to content

JSON extract

json_extract in SKDB

The json_extract construction in SKDB allows for the dynamic extraction of data from JSON-typed columns within a database table. This feature enables the creation of "reactive views" that remain up-to-date by automatically reflecting changes in the underlying JSON data.

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.