如何使用=运算符查询Postgres JSONB数组 [英] How to query Postgres JSONB arrays with = operator

问题描述

我正在寻找一种使用=子句查询postgres jsonb数组字段的方法.

I'm looking for a way to query postgres jsonb array field with kind of = clause.

假设我有一张桌子

CREATE TABLE events( id integer, tags jsonb, PRIMARY KEY(id) );

CREATE TABLE events( id integer, tags jsonb, PRIMARY KEY(id) );

具有类似于['Google', 'Hello World', 'Ruby']

我已经通过 Stackover流动并做类似的事情.

I have gone through Stackover Flow and done similar things.

形成的SQL就像SELECT "events".* FROM "events" WHERE ("events"."tags" @> '{google}') ORDER BY "business_events"."id" desc;

通过运行此命令，我得到此错误=>

By running this, I'm getting this error =>

ERROR: invalid input syntax for type json LINE 1: ...siness_events" WHERE ("business_events"."tags" @> '{google}'... ^ DETAIL: Token "google" is invalid. CONTEXT: JSON data, line 1: {google...

ERROR: invalid input syntax for type json LINE 1: ...siness_events" WHERE ("business_events"."tags" @> '{google}'... ^ DETAIL: Token "google" is invalid. CONTEXT: JSON data, line 1: {google...

有什么想法吗?

推荐答案

请注意，json对象的键和文本值用双引号引起来.

Note that keys and text values of json objects are enclosed in double quotes.

运算符按原样接受参数，因此无法使其不区分大小写.您可以使用功能jsonb_array_elements_text()完成此操作:

The operator takes arguments as they are and there is no way to make it work case insensitive. You can use the function jsonb_array_elements_text() to accomplish this:

SELECT events.*
FROM events 
CROSS JOIN jsonb_array_elements_text(tags)
WHERE lower(value) = 'google';

第二种解决方案价格昂贵，引用的答案中的注释也适用于此.

The second solution is much more expensive, notes in the cited answer apply here as well.

这篇关于如何使用=运算符查询Postgres JSONB数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！