json - PostgreSQL——NoSQL查询思路选择JSON的一个节点

Question

我的数据库中有这个 JSON:

  {
      "Id": 1,
      "Questions": [
        {
          "QuestionId": 6,
          "Description": "Question 1",
          "Alternatives": [
            {
              "Index": 1,
              "CorrectAnswer": false,
              "AlternativeId": 26,
              "QuestionId": 6,
              "Description": "Alternative one",
              "Selected": false
            },
            {
              "Index": 2,
              "CorrectAnswer": true,
              "AlternativeId": 27,
              "QuestionId": 6,
              "Description": "Alternative two",
              "Selected": false
            }
          ]
        },
        {
          "QuestionId": 7,
          "Description": "Question 2",
          "Alternatives": [
            {
              "Index": 1,
              "CorrectAnswer": false,
              "AlternativeId": 26,
              "QuestionId": 6,
              "Description": "Alternative one",
              "Selected": false
            },
            {
              "Index": 2,
              "CorrectAnswer": true,
              "AlternativeId": 27,
              "QuestionId": 6,
              "Description": "Alternative two",
              "Selected": false
            }
          ]
        }
      ]
    }

我不能在这份文件中得到一个问题。我尝试了以下查询:

select data#>'{Questions}' from db.my_table
where (data #> '{Questions,0,QuestionId}')::numeric = 6;

SELECT data ->> 'Questions' AS Questions FROM db.my_table
WHERE (data -> 'Questions' ->> 'QuestionId')::numeric = 6;

SELECT data ->> 'Questions' AS Questions FROM db.my_table
WHERE data  -> 'Questions' ->> 'QuestionId' = '6'

我做错了什么？我总是返回 0 个受影响的行。

文档:

https://www.postgresql.org/docs/current/static/datatype-json.html https://www.postgresql.org/docs/current/static/functions-json.html

欢迎大家的帮助!

Answer 1

让我们先谈谈您的每一个疑问:

select data#>'{Questions}' from db.my_table
where (data #> '{Questions,0,QuestionId}')::numeric = 6;

这实际上失败了(错误:无法将类型 json 转换为数字)。您可以将where子句中的运算符更改为#>>，这样就不会再报错了——#>>返回的是文本，可以转换为数字。然而，此时查询将返回整个 Questions 数组，而不是您想要的特定问题。

为什么？因为所有问题都属于同一行。

SELECT 返回行，其 WHERE 子句对行进行操作。由于一行中有很多值，因此使用这种方法有条件地提取 json 子对象时会遇到麻烦。

其他两个查询成功运行但没有产生结果:

SELECT data ->> 'Questions' AS Questions FROM db.my_table
WHERE (data -> 'Questions' ->> 'QuestionId')::numeric = 6;

SELECT data ->> 'Questions' AS Questions FROM db.my_table
WHERE data  -> 'Questions' ->> 'QuestionId' = '6'

在这两种情况下，您都在为 json blob 中不存在的字段编制索引。 Questions 是一个数组，因此无法用字符串对其进行索引，但 json 足够宽松，可以让您尝试。换句话说，子句 WHERE data -> 'Questions' ->> 'QuestionId' = '6' 根本不匹配任何行。

诀窍是将每个问题排成一行。这有点复杂，但做起来并不难。

首先看函数json_array_elements ，这似乎应该有效——它采用一个 json 数组并将每个元素作为一行返回，其中包含一个列(名为“值”)。乍一看，您似乎应该能够执行以下形式的操作:

SELECT value FROM json_array_elements(...)
WHERE value ->> 'QuestionId'::numeric = 6;

不幸的是，事情并没有那么简单。这是一个有效的查询:

SELECT datatable.question
  FROM my_table,
    json_array_elements(my_table.data -> 'Questions') AS datatable(question)
 WHERE (question->>'QuestionId')::numeric = 6;

好吧，让我们分解一下。最终，它返回我们使用 json_array_elements 调用创建的 datatable 表的 question 字段。 question 是一个 json 对象，我们过滤只返回 QuestionID == 6 的对象。

但请注意，我们也从 my_table 中进行选择，因为最终数据就是从那里来的。在本例中，我们在 my_table 和 datatable 之间进行无约束笛卡尔连接。这不好，而且可能不会很好地扩展。但它适用于我们目前只有几行的情况。

希望这能给你一个起点。