Error while getting particular object from jsonb in PostgreSQL

Question

I have created a table :

CREATE TABLE mytable (
    uuid int,
    answers jsonb
);

Inserted some JSON values and id :

   db.query('insert into mytable(uuid,answers) values($1,$2::jsonb)',
            [1,{ "user" : "1", "ans" : "Answer of 1" }])

I am using node js and pg as a client for Postgres.

The data is successfully entered and return the row as expected.

SELECT answers FROM mytable 
{"ans": "Answer of 1","user": "1"}

But when I tried to retrieve the value of "ans" of "user" with value 1, it returned an error:

SELECT
    elements ->> 'ans'
FROM
    mytable,
    jsonb_array_elements(answers) elements
WHERE 
    elements ->> 'user' = '1'

ERROR:  cannot extract elements from an object
SQL state: 22023

S-Man · Accepted Answer · 2019-08-04 14:08:42Z

4

In this question you inserted an JSON array: [{...}, {...}]. Now, the result set of your SELECT statement seems that you inserted a simple object: {...}. Of course then you cannot call jsonb_array_elements() which is for JSON arrays: demo with array, demo without array.

So, if you want to insert an JSON array, you need adjust your input parameter by adding the array braces around your JSON object: [...].

If you do not want to insert an array but a simple JSON object then you can go with this query which saves you the step for expanding the array. You can call the objects directly:

demo:db<>fiddle

SELECT
    uuid,
    answers ->> 'ans'
FROM
    mytable
WHERE 
    answers ->> 'user' = '1'

answered Aug 4, 2019 at 14:08

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Comments

Tajinder · Accepted Answer · 2019-08-04 13:25:14Z

2

Hoping, I understood your question correctly.

Please check below query.

SELECT
    answers ->> 'ans'
FROM
    mytable
where answers ->> 'user' = '1'
;

answered Aug 4, 2019 at 13:25

Tajinder

2,3484 gold badges38 silver badges58 bronze badges

Collectives™ on Stack Overflow

Error while getting particular object from jsonb in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related