I have the following log insights sql

fields jointStates.jointVariables.posSi.0 as pos0, jointStatesRef.jointVariables.posSi.0 as refPos0 
| stats 
avg((pos0-refPos0)*(pos0-refPos0)) as j0_pos_err,
 by bin(1s)

That i am trying to convert into the open search sql format . AI tools have given this to me

SELECT 
    DATE_HISTOGRAM(@timestamp, '1s') as time_bucket,
    AVG(POW(
        jointStates.jointVariables.posSi['0'] - jointStatesRef.jointVariables.posSi['0'], 
        2)
    ) as j0_pos_err
FROM simulation-telemetry
GROUP BY DATE_HISTOGRAM(@timestamp, '1s')
ORDER BY time_bucket;

But the syntax is wrong. Was hoping to get some assistance

I have the following log insights sql

fields jointStates.jointVariables.posSi.0 as pos0, jointStatesRef.jointVariables.posSi.0 as refPos0 
| stats 
avg((pos0-refPos0)*(pos0-refPos0)) as j0_pos_err,
 by bin(1s)

That i am trying to convert into the open search sql format . AI tools have given this to me

SELECT 
    DATE_HISTOGRAM(@timestamp, '1s') as time_bucket,
    AVG(POW(
        jointStates.jointVariables.posSi['0'] - jointStatesRef.jointVariables.posSi['0'], 
        2)
    ) as j0_pos_err
FROM simulation-telemetry
GROUP BY DATE_HISTOGRAM(@timestamp, '1s')
ORDER BY time_bucket;

But the syntax is wrong. Was hoping to get some assistance

• amazon-web-services
• amazon-opensearch
• aws-cloudwatch-log-insights

1 Answer 1

You can try following:

SELECT
   DATE_TRUNC('SECOND', `@timestamp`) time_bucket,
    INT(AVG(POW(
        get_json_object(`@message`, '$.jointStates.jointVariables.posSi.0') - get_json_object(`@message`, '$.jointStatesRef.jointVariables.posSi.0'), 
        2))
    ) as j0_pos_err
FROM `simulation-telemetry`
GROUP BY DATE_TRUNC('SECOND', `@timestamp`)
ORDER BY time_bucket desc

• Not sure how DATE_HISTOGRAM is working but DATE_TRUNC should be good to replace bin function.
• I don't know if it's possible to access jointStates/jointStatesRef fields directly from the root with OpenSearch SQL, but from @message, that's something possible.
• I added a cast to INT, because the result is a float, but you can adjust that part with your needs.

Some other OpenSearch SQL details here if you're insterested.