json - 查询Postgres中保存的Json数据

Question

我正在尝试查询保存在 postgres 中的 JSON 数据。表是这样创建的

CREATE TABLE ALARMDATA2(ALARM        CHAR(1300))

这是 JSON 对象:

{"delay_max": 0.0, "ts_errors": [{"count": 0, "state": 0, "is_priority1": true, "name": "SYNC", "is_priority2": false}, {"count": 0, "state": 0, "is_priority1": true, "name": "BYTE", "is_priority2": false}, {"count": 0, "state": 0, "is_priority1": true, "name": "PAT", "is_priority2": false}, {"count": 0, "state": 0, "is_priority1": true, "name": "CC", "is_priority2": false}, {"count": 0, "state": 0, "is_priority1": true, "name": "PMT", "is_priority2": false}, {"count": 0, "state": 0, "is_priority1": true, "name": "PID", "is_priority2": false}, {"count": 0, "state": 0, "is_priority1": false, "name": "TS", "is_priority2": true}, {"count": 0, "state": 0, "is_priority1": false, "name": "CRC", "is_priority2": true}, {"count": 0, "state": 0, "is_priority1": false, "name": "PCR", "is_priority2": true}, {"count": 0, "state": 0, "is_priority1": false, "name": "ACC", "is_priority2": true}, {"count": 0, "state": 0, "is_priority1": false, "name": "PTS", "is_priority2": true}, {"count": 0, "state": 0, "is_priority1": false, "name": "CAT", "is_priority2": true}], "is_stream_paused": false, "delay_min": 0.0, "ac_err": 0.0, "oj_err": 0.0}

我想根据“delay_max”(JSON 中的第一个条目)进行查询。我正在使用这个查询

SELECT ALARM->>'delay_max' AS delay_max FROM alarmdata2;

我认为根据这些链接(here 和 here)查询语法没问题，但我收到了这个错误

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

我找了好久，还是没找到。任何建议为什么？

Answer 1

您必须将 ALARM 列定义为 JSON 或 JSONB 类型:

创建表警报数据 2(警报 JSONB)


然后它就会起作用。