如何从PostgreSQL中的jsonb获取特定对象? [英] How to get particular object from jsonb in PostgreSQL?

问题描述

我有一个名为"Test"的表，其中包含两个字段"qnId"和"Answers". "qnId"存储一个uuid，"Answers"是一个jsonb数组，大致如下所示:

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:

[{ "user" : "1", "ans" : "some text" }, { "user" : "3", "ans": "some text"}]

如何获取值为3的"user"的"ans"值. 如何使用普通的SQL查询检索值

how can I retrieve the value of "ans" of "user" with value 3. How can I retrieve the value using normal SQL queries

推荐答案

演示:db< >小提琴

您可以使用jsonb_array_elements()将数组元素扩展为每一行.之后，您可以使用->>运算符(文档):

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'

这篇关于如何从PostgreSQL中的jsonb获取特定对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！