Rails:PostgreSQL DB中的搜索阵列JSON字段

Question

认真努力提出一个理性的范围。

我想获得一个模型列表，该模型使用PostgreSQL。

有人可以将我指向正确的方向，而且，我可以将这是一种班级方法而不是范围，我只需要所有模型的列表。

主要模型是提交，has_one：fmp_session- fmpsession -fmpsession具有一个字段：project_data，是postgresql：json type字段，包含一个JSON数组。

因此，如果fmp_session.project_data'type'json键等于'crew'。

我宁愿坚持使用activerecord或sql（或混合与匹配），然后让阿雷尔独自一人。

我在想这样的事情：

Submission.joins(:fmp_session).where(
    'EXISTS(
        SELECT 1 from json_array_elements(
            "project_data"
            ) project_data WHERE (
            project_data#>> "{type}"
        ) = "CREW"
    )'
)

从理论上讲，这将搜索project_data数组，并用＃>>搜索它，因此数组中有多少个元素都没关系 - 但是我尝试了Query的40个阴谋和AM卡住。帮助我找到合适的查询，将一个表与另一个表连接到另一个表格并搜索JSON数组字段以查找特定的字符串。

update

要显示数据库部分，这是表描述（来自PSQL）和此表的schema.db段。另外，您应该知道

 Column        | Type      |Modifiers
---------------+-----------+-----------------------------------------------------------
 id            | integer   | not null default nextval('fmp_sessions_id_seq'::regclass)
 project_data  | json      | default '[]'::json

和架构

  create_table "fmp_sessions", force: :cascade do |t|
    t.json     "project_data",        default: []
    t.json     "project_member_data", default: []
    t.datetime "created_at",                       null: false
    t.datetime "updated_at",                       null: false
  end

Answer 1

可能在引号中的问题：

Submission.joins(:fmp_session).where(
    "EXISTS(
        SELECT 1 from json_array_elements(
            project_data
            ) project_data WHERE (
            project_data#>> '{type}'
        ) = 'CREW'
    )"
)

upd：回答评论

查询很有用：

SELECT project_data#>> '{name}'
 from json_array_elements(
'[
            {
                "id": "1",
                "name": "First",
                "type": "CREW"
            },
            {
                "id": "2",
                "name": "Second",
                "type": "NOCREW"
            },
            {
                "id": "3",
                "name": "Third",
                "type": "CREW"
            }
]'
            ) project_data
WHERE project_data#>>'{type}' = 'CREW'
;

 ?column? 
----------
 First
 Third
(2 rows)

SELECT EXISTS (SELECT 1
 from json_array_elements(
'[
            {
                "id": "1",
                "name": "First",
                "type": "CREW"
            },
            {
                "id": "2",
                "name": "Second",
                "type": "NOCREW"
            },
            {
                "id": "3",
                "name": "Third",
                "type": "CREW"
            }
]'
            ) project_data
WHERE project_data#>>'{type}' = 'CREW'
)

 exists 
--------
 t
(1 row)

更新2：PG12功能。

pg12添加了一些允许在JSON中使用阵列的精彩烈火：

SELECT project_data @? '$[*] ? (@.type == "CREW")' AS res
  FROM (SELECT '[
        {"id": "1","name": "First","type": "CREW"},
        {"id": "2","name": "Second","type": "NOCREW"},
        {"id": "3","name": "Third","type": "CREW"}
       ]'::jsonb AS project_data) AS j;
                                                             
res
----------
 t
(1 row)