Extract value of single string record from PostgreSQL JSON column

Question

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?

John Klakegg · Accepted Answer · 2016-10-14 15:23:30Z

6

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'

answered Oct 14, 2016 at 15:23

John Klakegg

9439 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Extract value of single string record from PostgreSQL JSON column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related