ruby-on-rails - 为 jsonb 值搜索构建 Activerecord/SQL 查询

Question

目前，对于使用不同参数的循环搜索，我构建了这个 ActiveRecord 查询:

current_user.documents.order(:updated_at).reverse_order.includes(:groups,:rules)

现在，通常我会添加一个 where 子句来执行此搜索。但是，我现在需要通过 jsonb 字段搜索具有特定 value 的所有行，如在键:值对中。我已经能够使用以下语法执行与我的 SQL 中类似的操作(数据字段只会嵌套两层):

SELECT 
    * 
FROM 
    (SELECT 
         * 
    FROM 
        (SELECT 
            * 
        FROM 
            documents
        ) A, 
        jsonb_each(A.data)
    ) B, 
    jsonb_each_text(B.value) ASC C 
WHERE 
    C.value = '30';

但是，我想使用当前的 ActiveRecord 搜索来进行此查询(其中包括组/规则预加载)。

我正在努力使用逗号，我理解这是一个隐式连接，它在显式连接之前执行，所以当我尝试这样的事情时:

select * from documents B join (select * from jsonb_each(B.data)) as A on true;
ERROR:  invalid reference to FROM-clause entry for table "b"
LINE 1: ...* from documents B join (select * from jsonb_each(B.data)) a...
                                                           ^
HINT: There is an entry for table "b", but it cannot be referenced from this part of the query.

但我不明白如何在进行联接调用之前引用我创建的 ActiveRecord 查询的完整“表”，以及如何使用逗号语法使隐式联接起作用。

此外，我是一名 SQL 业余爱好者，所以如果您看到一些改进或其他方法可以做到这一点，请告诉我。

编辑:文档表的描述:

                                                        Table "public.documents"
Column      |            Type             |                       Modifiers                        | Storage  | Stats target | Description 
------------+-----------------------------+--------------------------------------------------------+----------+--------------+-------------
id          | integer                     | not null default nextval('documents_id_seq'::regclass) | plain    |              | 
document_id | character varying           |                                                        | extended |              | 
name        | character varying           |                                                        | extended |              | 
size        | integer                     |                                                        | plain    |              | 
last_updated| timestamp without time zone |                                                        | plain    |              | 
user_id     | integer                     |                                                        | plain    |              | 
created_at  | timestamp without time zone |                                                        | plain    |              | 
updated_at  | timestamp without time zone |                                                        | plain    |              | 
kind        | character varying           |                                                        | extended |              | 
uid         | character varying           |                                                        | extended |              | 
access_token_id | integer                     |                                                        | plain    |              | 
data        | jsonb                       | not null default '{}'::jsonb                           | extended |              | 

索引: "documents_pkey"主键，btree (id)```

示例行，首先匹配“30”的搜索(数据是最后一个字段):

2104 | 24419693037                                          | LsitHandsBackwards.jpg                   |        |                         |       1 | 2017-06-25 21:45:49.121686 | 2017-07-01 21:32:37.624184 | box          | 221607127         |              15 | {"owner": {"born": "to make history", "price": 30}}
2177 | /all-drive/uml flows/typicaluseractivity.svg         | TypicalUserActivity.svg                  |  12375 | 2014-08-11 02:21:14     |       1 | 2017-07-07 14:00:11.487455 | 2017-07-07 14:00:11.487455 | dropbox      | 325694961         |              20 | {"owner": {}}

Answer 1

您可以使用类似于您已经展示的查询:

SELECT
    d.id, d.data
FROM
    documents AS d 
    INNER JOIN json_each(d.data)  AS x ON TRUE 
    INNER JOIN json_each(x.value) AS y ON TRUE 
WHERE
    cast(y.value as text) = '30';

假设您的数据如下:

INSERT INTO documents
    (data)
VALUES
   ('{"owner": {"born": "to make history", "price": 30}}'),
   ('{"owner": {}}'),
   ('{"owner": {"born": "to make history", "price": 50}, "seller": {"worth": 30}}')
;

你得到的结果是:

id | data                                                                        -: | :--------------------------------------------------------------------------- 1 | {"owner": {"born": "to make history", "price": 30}}                          3 | {"owner": {"born": "to make history", "price": 50}, "seller": {"worth": 30}}

您可以在 dbfiddle here 查看它(连同一些逐步查看的数据)