How to return value for key from a jsonb column?

Question

I've successfully created the below table on 9.5.1.0:

 CREATE TABLE json_test (
 id serial primary key,
 data jsonb
 );

and successfully inserted this record:

 INSERT INTO json_test (data) VALUES ('{"0":"INVALID","1":"BELOW","2":"EQUAL","3":"ABOVE"}')

and I am successfull in returning the entire record that I inserted via:

 SELECT * FROM json_test WHERE data::jsonb ? '1';

but how do I return the value associated with '1', i.e. "BELOW" ?

Answer 1

This post gives you your answer:

CREATE TABLE json_test 
(
 id serial primary key,
 data jsonb
);
INSERT INTO json_test (data) VALUES ('{"0":"INVALID","1":"BELOW","2":"EQUAL","3":"ABOVE"}');

You specify the element that you wish to extract thus:

SELECT id, data->>'1' AS whatever FROM json_test;

And the result is:

id whatever
1 BELOW

There are a shedload of new json functions in PostgreSQL - there could be more examples (hint PostgreSQL team...).