I have a table called ‘Test’ that holds two fields ‘qnId’ and ‘Answers’. ‘qnId’ stores a uuid and ‘Answers’ is a jsonb array that roughly looks like this:
x
[{ "user" : "1", "ans" : "some text" }, { "user" : "3", "ans": "some text"}]
how can I retrieve the value of "ans"
of "user"
with value 3
.
How can I retrieve the value using normal SQL queries
Advertisement
Answer
You can use jsonb_array_elements()
to expand the array elements into one row each. Afterwards you are able to filter the right elements using the ->>
operator (documentation):
SELECT
uuid,
elements ->> 'ans'
FROM
mytable,
jsonb_array_elements(answers) elements
WHERE
elements ->> 'user' = '3'