titel titel - 11 months ago 67
JSON Question

Extract value of single string record from PostgreSQL JSON column

For a given Postgres table:

CREATE TABLE "public"."store" (
"key" varchar(50) NOT NULL,
"value" json,

If I populate the following JSON encoded values:

INSERT INTO "store" VALUES ('integer', '1'),
('array', '{"foo": "bar"}'),
('string', '"baz"');

I can write a query to extract the value of foo like so:

SELECT value->>'foo' FROM store WHERE key = 'array'

And it returns the string value of bar (with no quotes).

However, I cannot figure it out is how should I write the query to get the unencoded value of the string key. The following query returns "baz" (with quotes).

SELECT value FROM store WHERE key = 'string'

How should I write this last query to extract the single string value of the 'string' key?

Answer Source

Use the #>> operator, from the docs: Get JSON object at specified path as text.

SELECT value #>>'{}' FROM store WHERE key = 'string'

Result: baz and not "baz".


You can also do the same when key = 'array':

SELECT value #>>'{foo}' FROM store WHERE key = 'array'