titel titel - 1 month ago 11
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,
PRIMARY KEY ("key")
);


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

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


EDIT:

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

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