Document jsonExtract

[jberdine]

Migrate skdb documentation:

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.