0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.