Filter rows by given key/value pair in a jsonb column

Question

I have the next table in Postgres 9.4

CREATE TABLE public.neuro (
    nid int4 NOT NULL DEFAULT nextval('neuro_nid_seq'::regclass),
    data jsonb,
    CONSTRAINT neuro_pkey PRIMARY KEY (nid) NOT DEFERRABLE INITIALLY IMMEDIATE
);

with the records:

{"item2": {"1": "0", "uid": "0", "nota": "weqcqwe qwe wq", "fecha": "23-02-2015", "examen": "aesc", "puntaje": "0", "paciente": "103636426"}}
{"item2": {"1": "0", "uid": "0", "nota": "text", "fecha": "23-02-2015", "examen": "aesc", "puntaje": "0", "paciente": "103636426"}}
{"item3": {"1": "3", "2": "1", "3": "3", "uid": "0", "fecha": "23-02-2015", "examen": "fab", "puntaje": "7", "paciente": "103636426"}}
...

How to select all records with examen ='aesc'? I tried using the operators ->> or @. How to select some fields in the same way? I need to maintain the initial "itemX" in a jSON column.

with your data setup, I'm not quite sure of a very efficient mehod-- however, you could use "like" on the text representation of the json... — Joe Love, Feb 23 '15 at 19:06

score 3 · Answer 1 · edited May 23 '17 at 11:43

Since your JSON objects seem to be nested exactly one level, you could unnest one level with jsonb_each() and search the value part of the dynamic key name (which we ignore this way):

SELECT n.*
FROM   public.neuro n, jsonb_each(n.data) d
WHERE  d.value->>'examen' = 'aesc'

Filter rows by given key/value pair in a jsonb column

1 Answers1