1 of 3

JSON

This page describes the JSON functions in Lenses SQL.

JSON_EXTRACT_FIRST

This page describes the JSON_EXTRACT_FIRST function in Lenses SQL.

JSON_EXTRACT_FIRST (json_string,pattern)

Interprets ‘pattern’ as a Json path pattern and applies it to ‘json_string’, returning the first match, as a string containing valid json. Examples for the pattern parameter: “$.a”, “$[‘a’]”, “$[0]”, “$.points[?(@[‘id’]==‘i4’)].x”, “$[‘points’][?(@[‘y’] >= 3)].id”, “$.conditions[?(@ == false)]”

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    JSON_EXTRACT_FIRST(time_details, '$.hour'), 
    time_details 
FROM credit-card-transactions
LIMIT 2;

Output:

{
  "value": {
    "JSON_EXTRACT_FIRST": "1",
    "time_details": "{\"hour\": 1, \"minute\": 17, \"second\": 15, \"millisecond\": 36}"
  }
}
{
  "value": {
    "JSON_EXTRACT_FIRST": "16",
    "time_details": "{\"hour\": 16, \"minute\": 23, \"second\": 2, \"millisecond\": 255}"
  }
}

JSON_EXTRACT_ALL

This page describes the JSON_EXTRACT_ALL function in Lenses SQL.

JSON_EXTRACT_ALL (json_string,pattern)

Interprets ‘pattern’ as a Json path pattern and applies it to ‘json_string’, returning all matches, as an array of strings containing valid json. Examples for the pattern parameter: “$.a”, “$[‘a’]”, “$[0]”, “$.points[?(@[‘id’]==‘i4’)].x”, “$[‘points’][?(@[‘y’] >= 3)].id”, “$.conditions[?(@ == false)]”

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    JSON_EXTRACT_ALL(time_details, '$.*'), 
    time_details 
FROM credit-card-transactions
LIMIT 2;

Output:

{
  "value": {
    "JSON_EXTRACT_ALL": [
      "1",
      "17",
      "15",
      "36"
    ],
    "time_details": "{\"hour\": 1, \"minute\": 17, \"second\": 15, \"millisecond\": 36}"
  }
}
{
  "value": {
    "JSON_EXTRACT_ALL": [
      "16",
      "23",
      "2",
      "255"
    ],
    "time_details": "{\"hour\": 16, \"minute\": 23, \"second\": 2, \"millisecond\": 255}"
  }
}

JSON_EXTRACT_ALL

This page describes the JSON_EXTRACT_ALL function in Lenses SQL.

JSON_EXTRACT_ALL (json_string,pattern)

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    JSON_EXTRACT_ALL(time_details, '$.*'), 
    time_details 
FROM credit-card-transactions
LIMIT 2;

Output:

{
  "value": {
    "JSON_EXTRACT_ALL": [
      "1",
      "17",
      "15",
      "36"
    ],
    "time_details": "{\"hour\": 1, \"minute\": 17, \"second\": 15, \"millisecond\": 36}"
  }
}
{
  "value": {
    "JSON_EXTRACT_ALL": [
      "16",
      "23",
      "2",
      "255"
    ],
    "time_details": "{\"hour\": 16, \"minute\": 23, \"second\": 2, \"millisecond\": 255}"
  }
}

JSON_EXTRACT_FIRST

This page describes the JSON_EXTRACT_FIRST function in Lenses SQL.

JSON_EXTRACT_FIRST (json_string,pattern)

Available in:

Processors

SQL Studio

Sample code:

USE `kafka`;
SELECT 
    JSON_EXTRACT_FIRST(time_details, '$.hour'), 
    time_details 
FROM credit-card-transactions
LIMIT 2;

Output:

{
  "value": {
    "JSON_EXTRACT_FIRST": "1",
    "time_details": "{\"hour\": 1, \"minute\": 17, \"second\": 15, \"millisecond\": 36}"
  }
}
{
  "value": {
    "JSON_EXTRACT_FIRST": "16",
    "time_details": "{\"hour\": 16, \"minute\": 23, \"second\": 2, \"millisecond\": 255}"
  }
}