Spaces:
Running
Running
File size: 1,890 Bytes
89acd07 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | 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 |