Spaces:
Running
Running
| WITH asset_attrs AS ( | |
| SELECT | |
| name AS asset_name, | |
| ( | |
| SELECT jsonb_object_agg(key, val->'value') | |
| FROM jsonb_each(attributes::jsonb) AS x(key, val) | |
| WHERE val->'value' IS NOT NULL | |
| AND key NOT IN ('notes', 'location', 'info', 'displayIndex', 'indexConfig') | |
| ) AS sensor_data, | |
| ( | |
| SELECT to_timestamp(max((val->>'timestamp')::bigint) / 1000.0) | |
| FROM jsonb_each(attributes::jsonb) AS x(key, val) | |
| WHERE val->>'timestamp' IS NOT NULL AND val->>'timestamp' ~ '^[0-9]+$' | |
| ) AS attrs_timestamp | |
| FROM openremote.asset | |
| WHERE id = '4QJnSuo833PPbU5oGYIZus' | |
| ORDER BY version DESC LIMIT 1 | |
| ), | |
| latest_datapoints AS ( | |
| SELECT DISTINCT ON (attribute_name) | |
| attribute_name, | |
| value, | |
| timestamp | |
| FROM openremote.asset_datapoint | |
| WHERE entity_id = '4QJnSuo833PPbU5oGYIZus' | |
| ORDER BY attribute_name, timestamp DESC | |
| ), | |
| aggregated_datapoints AS ( | |
| SELECT | |
| COALESCE(jsonb_object_agg(attribute_name, value), '{}'::jsonb) AS sensor_data, | |
| max(timestamp) AS dp_timestamp | |
| FROM latest_datapoints | |
| ) | |
| SELECT | |
| asset_attrs.asset_name, | |
| NOW() + INTERVAL '3 hours' AS local_time, | |
| (COALESCE(asset_attrs.sensor_data, '{}'::jsonb) || | |
| COALESCE(aggregated_datapoints.sensor_data, '{}'::jsonb) || | |
| CASE | |
| WHEN aggregated_datapoints.dp_timestamp IS NOT NULL AND asset_attrs.attrs_timestamp IS NOT NULL THEN | |
| jsonb_build_object('timestamp', GREATEST(aggregated_datapoints.dp_timestamp, asset_attrs.attrs_timestamp)) | |
| WHEN aggregated_datapoints.dp_timestamp IS NOT NULL THEN | |
| jsonb_build_object('timestamp', aggregated_datapoints.dp_timestamp) | |
| WHEN asset_attrs.attrs_timestamp IS NOT NULL THEN | |
| jsonb_build_object('timestamp', asset_attrs.attrs_timestamp) | |
| ELSE | |
| '{}'::jsonb | |
| END | |
| ) AS sensor_data | |
| FROM asset_attrs | |
| CROSS JOIN aggregated_datapoints |