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.
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 4 months ago