PostgreSQL – How to Return Value for Key from JSONB Column

jsonpostgresql

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" ?

Best Answer

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...).

Related Question