sql - 在 ActiveRecord 中搜索 jsonb 列

Question

我的用户模型有 jsonb 列，名称是 raw。它看起来像这样:

{"client"=>{"tokens"=>["asdasd"]}}

现在我想通过 raw["client"]["tokens"] 中的 token 查找用户。我该怎么做？

Answer 1

我通常首先在 SQL 控制台中编写此类查询，然后将其转换为 ActiveRecord。

您可以在 Postgres 中导航散列键。查询

SELECT
  raw #> '{client,tokens}'
FROM users

将只返回该路径中的 tokens 数组。现在我们需要检查它是否包含我们正在寻找的值。查询

SELECT
  raw #> '{client,tokens}' ? 'asdasd'
FROM users

将为那些具有匹配标记的行选择t。现在您可以将其移动到 WHERE 部分:

SELECT
*
FROM users
WHERE  raw #> '{client,tokens}' ? 'asdasd'

如果这选择了您所期望的，那么您可以将其转换为 AR:

User.where("config #> '{client, tokens}' ? :token", token: 'asdasd')

请注意，我不能使用 ? 进行参数替换，而是使用 :token 代替。另请注意，这仅适用于 JSONB (Postgres 9.4+ https://www.postgresql.org/docs/9.4/static/functions-json.html#FUNCTIONS-JSONB-OP-TABLE )

更新:

你应该(tm)(我没有测试过这个)相处:

CREATE INDEX index_tokens ON users USING GIN ((raw #> '{client, tokens}'));

参见 https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-INDEXING了解更多详情

更新2:

另一种查询方式是:

raw @> '{"client": {"tokens": ["asdasd"]}}'   

它应该能够在原始列上使用一个简单的 GIN 索引(它比上面描述的表达式索引使用更多的空间)。

使用 GIN(原始)为用户创建索引 index_user_raw

再次说明:详情请参阅上面的 JSON INDEXING 链接。并使用查询可视化工具查看差异。我喜欢http://tatiyants.com/pev