json - 过滤存储在 PostgreSQL 表中的 JSON 内部键

Question

我有一个 JSON 格式的报告存储在 PostgreSQL 数据库表的一个字段中。假设(简化的)表格格式是:

      Column       |            Type
-------------------+----------------------------
 id                | integer
 element_id        | character varying(256)
 report            | json

报表中的数据结构是这样的

{
  "section1":
    "test1": {
      "outcome": "nominal",
      "results": {
        "value1": 34.,
        "value2": 56.
      }
    },
    "test2": {
      "outcome": "warning",
      "results": {
        "avg": 4.5,
        "std": 21.
      }
    },
    ...
  "sectionN": {
    ...
  }      
}    

也就是说，在第一层(部分)有 N 个键，每个键都是一个具有一组键(测试)的对象，具有结果 和结果 的变量集，采用(key, value) 对的形式。

我需要根据内部 JSON 键进行过滤。更具体地说，在这个例子中，我想知道是否有可能单独使用 SQL 来获取元素，例如 results 中的 std 值高于某个阈值的部分，比如 10。我什至可以知道 std 在 test2 中，但我不知道 a priori 在哪个部分。例如，使用此过滤器 (test2.std > 10.)，将出现带有上面所示示例数据的记录，因为 test2 中的 std 变量 test 的这个值等于 21. (>10.)。

另一个更简单的过滤器可能是请求所有 test2.outcome 不是 nominal 的记录。

Answer 1

一种方式是jsonb_each，比如:

select  section.key
,       test.key
from    t1
cross join
        jsonb_each(t1.col1) section
cross join
        jsonb_each(section.value) test
where   (test.value->'results'->>'std')::int > 10

Example at SQL Fiddle.