PostgreSQL 9.4 jsonb如何查询

Question

我目前有一个 Sheet 模型，具有特定的 jsonb 属性:

      create_table "sheets", force: :cascade do |t|
        t.string   "name"
        t.integer  "user_id",    default: 0,    null: false
        t.boolean  "private",    default: true, null: false
        t.jsonb    "t_data",     default: {},   null: false
      end

在 t_data jsonb 属性中，我将存储一个列表(多行)，其内容应如下所示:

    def sample_3_content
      {
        headings: [
          "Rendering engine",
          "Browser",
          "Platform",
          "Engine version",
          "CSS grade"
        ],
        rows: {
                "line_0":{
                  "Rendering engine":"Trident",
                  "Browser":"Internet Explorer 4.0",
                  "Platform":"Win 95+",
                  "Engine version":"4.0",
                  "CSS grade":"X"
                },            
                "line_1":{
                  "Rendering engine":"Trident",
                  "Browser":"Internet Explorer 5.0",
                  "Platform":"Win 95+",
                  "Engine version":"5",
                  "CSS grade":"C"
                }, 

这个查询

Sheet('rows @> ?', {"CSS grade" => "X" }.to_json)

出现错误

NoMethodError: undefined method `Sheet' for main:Object

我不确定这是最好的模式...因为我想查询不同标题上的所有行，即“CSS grade”==“X”，“Browser”包含“Explorer”...

欢迎任何重写我的模式的建议......

Answer 1

我的建议是不要使用 "line_0" 键，因为它在您为 JSONB 内容列提供的结构中看起来过多。将普通数组用于 "rows" 键会更加灵活。带有示例数据的示例 DDL 可能类似于:

CREATE TABLE sheets (content JSONB);
INSERT INTO sheets VALUES
    ('{
     "headings": [
       "Rendering engine",
       "Browser",
       "Platform",
       "Engine version",
       "CSS grade"
     ],
     "rows": [{
                  "Rendering engine":"Trident",
                  "Browser":"Internet Explorer 4.0",
                  "Platform":"Win 95+",
                  "Engine version":"4.0",
                  "CSS grade":"X"
        },
        {
                  "Rendering engine":"Trident",
                  "Browser":"Internet Explorer 5.0",
                  "Platform":"Win 95+",
                  "Engine version":"5",
                  "CSS grade":"C"
        }
     ]
     }'::JSONB);

在这种情况下，与显式声明 row->'line_X'->>'jsonAttribute' 相比，您可以更自由地查询数组元素。示例查询可以是以下内容:

SELECT * FROM sheets,jsonb_array_elements(content->'rows') as row
WHERE row->>'CSS grade' = 'X' 
AND row->>'Platform' = 'Win 95+';