Query JSON String Fields in SQL
Use json_value
in SQL queries to return the value of a JSON property within a JSON string column.
ChaosSearch SQL includes initial support for the Trino json_value
operator that uses a JSONPath expression to return the value of a JSON property indexed within a JSON string column of a view. This behavior is similar to the Materialize with JSONPath view transform that uses a JSONPath expression to materialize a new view column for a JSON property extracted from a JSON string.
Querying with json_value
json_value
When you query a view that has columns with indexed JSON strings, you can return the value for a specific JSON property by referencing the view column name and a JSONPath expression for the desired property.
As an example, in CloudTrail log files, there is a Records.requestParameters
property that could be formatted as an array with all of the associated parameters sent with the logging request. The list of parameters (JSON properties) varies for each AWS service. To avoid the JSON record permutation explosion, the Record.requestParameters
is typically indexed as a JSON string field, where the JSON string contains the concatenated list of all the associated properties.
If you wanted to create a SQL query to display the value for a specific property in the Record.requestParameters
JSON string, you can use the json_value
operator as in the following example:
select json_value("Records.requestParameters", '$.includeShadowTrails') as includeShadowTrails from my-json-view;
The following screen shows a sample SQL Editor query that uses json_value
to display the embedded includeShadowTrails
property value:
See the json_value syntax description for more information about the function.
Updated 7 months ago