django rawsql postgres嵌套json/jsonb查询

Question

我在postgres上有一个名为data的jsonb结构，其中每一行(大约有300万行)如下所示：

[
    {
        "number": 100,
        "key": "this-is-your-key",
        "listr": "20 Purple block, THE-CITY, Columbia",
        "realcode": "LA40",
        "ainfo": {
            "city": "THE-CITY",
            "county": "Columbia",
            "street": "20 Purple block",
            "var_1": ""
        },
        "booleanval": true,
        "min_address": "20 Purple block, THE-CITY, Columbia LA40"
    },
    .....
]

我想以最快的方式查询min_address字段。在Django中，我尝试使用：

APModel.objects.filter(data__0__min_address__icontains=search_term)

但这需要很长时间才能完成(另外，“-CITY”是大写的，所以我必须在这里使用icontains。我试着像这样使用rawsql：

cursor.execute("""\
    SELECT * FROM "apmodel_ap_model" 
    WHERE ("apmodel_ap_model"."data" 
    #>> array['0', 'min_address'])
    @> %s \
    """,\
    [json.dumps([{'min_address': search_term}])]
)

但这给我抛出了奇怪的错误，比如：

LINE 4:       @> '[{"min_address": "some lane"}]'       
              ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

我想知道使用rawsql游标查询字段min_address的最快方法是什么。

Answer 1

回答晚了，可能对操作不再有帮助了。另外，我根本不是Postgres/JSONB方面的专家，所以这可能是一个糟糕的想法。

给定此设置；

so49263641=# \d apmodel_ap_model;
         Table "public.apmodel_ap_model"
 Column | Type  | Collation | Nullable | Default
--------+-------+-----------+----------+---------
 data   | jsonb |           |          |

so49263641=# select * from apmodel_ap_model ;
                                           data
-------------------------------------------------------------------------------------------
 [{"number": 1, "min_address": "Columbia"}, {"number": 2, "min_address": "colorado"}]
 [{"number": 3, "min_address": "  columbia "}, {"number": 4, "min_address": "California"}]
(2 rows)

下面的查询将对象从data数组“扩展”到单独的行。然后，它将模式匹配应用于min_address字段。

so49263641=# SELECT element->'number' as number, element->'min_address' as min_address 
    FROM apmodel_ap_model ap, JSONB_ARRAY_ELEMENTS(ap.data) element 
    WHERE element->>'min_address' ILIKE '%col%';
 number |  min_address
--------+---------------
 1      | "Columbia"
 2      | "colorado"
 3      | "  columbia "
(3 rows)

但是，我怀疑它在大型数据集上是否能很好地执行，因为min_address值在模式匹配之前被强制转换为文本。

JSONB ：关于为搜索https://stackoverflow.com/a/33028467/1284043索引数据的一些很好的建议