时间戳

首页 > 解决方案 > 过滤存储在 PostgreSQL 表中的 JSON 内部键

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

问题描述

我有一个 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 个键（sections），每个键都是一个带有一组键（tests）的对象，带有一个结果和一组成对的变量结果(key, value)。

我需要根据内部 JSON 键进行过滤。更具体地说，在这个例子中，我想知道是否有可能单独使用 SQL 来获取例如结果std部分中的值高于某个阈值（例如 10）的元素。我什至可以知道在 中，但我不知道先验在哪个部分。例如，使用此过滤器 ( )，将显示带有上述示例数据的记录，因为测试中的变量的值等于 21. (>10.)。stdtest2test2.std > 10.stdtest2

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

标签： jsonpostgresqlfilter

解决方案

一种方法是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

SQL Fiddle 的示例。

推荐阅读