CODEWITHSUNDEEP
sqljsonpostgresqlpostgresql-9.5node-postgres
Manu
Manu

Reputation: 791

Error while getting particular object from jsonb in PostgreSQL

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

Upvotes: 2

Views: 2874

Answers (2)

S-Man
S-Man

Reputation: 23666

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'

Upvotes: 4

Tajinder
Tajinder

Reputation: 2338

Hoping, I understood your question correctly.

Please check below query.

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

Upvotes: 2

Related Questions