Query JSON String Fields in SQL

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

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.