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}"
  }
}