Query Json data saved in Postgres

Question

I'm trying to query JSON data saved in postgres. This is how the table is created

CREATE TABLE ALARMDATA2(ALARM        CHAR(1300))

This is the JSON object:

{"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}

I'd like to query based on "delay_max" (the first entry in JSON). I am using this query

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

I think the query syntax is fine as per these links (here and here) but I am getting this error

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

I've been searching for a while but I have not clue. Any suggestions why ?

Never ever use char() and for JSON use - well - json (or jsonb) — user330315
– user330315, Commented Jan 13, 2017 at 21:11
Almost never use char() anyway in PostgreSQL. Unless you can know the size, it can't grow, and the input isn't whitespace padded on either side; or, if you want the whitespace padding (which is almost always bad). — Evan Carroll
– Evan Carroll, Commented Jan 13, 2017 at 21:17

Marc Balmer · Accepted Answer · 2017-01-13 21:05:39Z

1

You must define the ALARM colum as type JSON or JSONB:

CREATE TABLE ALARMDATA2(ALARM JSONB)

And then it will work.

answered Jan 13, 2017 at 21:05

Marc Balmer

1,8301 gold badge12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Evan Carroll · Accepted Answer · 2017-01-13 21:19:25Z

1

Fix the problem

ALTER TABLE alarmdata2
ALTER COLUMN alarm
  TYPE jsonb
  USING alarm::jsonb;

Or, work around it

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

edited Jan 13, 2017 at 21:19

answered Jan 13, 2017 at 21:11

Evan Carroll

1

Collectives™ on Stack Overflow

Query Json data saved in Postgres

2 Answers 2

Comments

Fix the problem

Or, work around it

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Fix the problem

Or, work around it

Comments

Your Answer

Sign up or log in

Post as a guest

Related