如何在jsonb postgresql中查询并转换为谓词JPA [英] How to query in jsonb postgresql and convert to predicate JPA

问题描述

我卡住了:)

没有人知道如何在jsonb中查询postgresql 我有表USER

does anyone know how query postgresql in jsonb i have table USER

• INT ID，
• Varchar名称
• jsonb categoryId

categoryId字段中的示例数据，像这样= [1,2,3,4,5]

the example data in categoryId field like this = [1,2,3,4,5]

我尝试了以下有效的查询:

I have tried this query which works:

select * 
from user where categoryId @> '2'::jsonb ;  

但是如何使用

select * 
from user 
where categoryId @> '1,3,4'::jsonb

并且我将实现它以休眠jpa/jpql-predicate，但我想首先了解本机查询

and i will implement this to hibernate jpa/jpql-predicate, but i want to know native query first

非常感谢

推荐答案

在SQL中，您可以使用?|

In SQL you can use the ?| operator for this

select *
from "user"
where categoryid ?| array['1','3','4'];

这将返回行，其中JSON数组中至少包含一个值.如果要查找包含所有值的值，请使用?&运算符.

That will return rows where at least one of the values is contained in the JSON array. If you want to find those that contain all values, use the ?& operator instead.

要使用@>运算符，您需要在右侧使用JSON数组:

To use the @> operator you would need to use a JSON array on the right hand side:

select *
from "user"
where categoryid @> '[1,3,4]'::jsonb

---

请注意，user是保留关键字.每次要引用该表时，都必须用双引号将其引起来.我强烈建议您找到其他表名.

---

Note that user is a reserved keyword. You will have to enclose it in double quotes every time you want to refer to the table. I would highly recommend to find a different table name.

这篇关于如何在jsonb postgresql中查询并转换为谓词JPA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！