主页 / dba / 问题 / 342066
Accepted
vikrant rana
Asked: 2024-08-31 20:30:36 +0800 CST

无法提取 json 列数据（posgres 9.6）

  • 772

我可以运行以下查询来获取给定格式的数据：

SELECT to_timestamp(unnest(ARRAY[[1725042600, 1725043500], [1725041700, 1725042600], [1725043500, 1725044400], [1725040800, 1725041700]]));

      to_timestamp
------------------------
 2024-08-30 18:30:00+00
 2024-08-30 18:45:00+00
 2024-08-30 18:15:00+00
 2024-08-30 18:30:00+00
 2024-08-30 18:45:00+00
 2024-08-30 19:00:00+00
 2024-08-30 18:00:00+00
 2024-08-30 18:15:00+00
(8 rows)

我使用的是 postgres 9.6 及以下版本架构

test_database=> \d fact_completeness_count_requests
                    Table "public.fact_completeness_count_requests"
         Column          |            Type             | Collation | Nullable | Default
-------------------------+-----------------------------+-----------+----------+---------
 request_id              | character varying(64)       |           | not null |
 event_type              | character varying(255)      |           | not null |
 technology              | character varying(255)      |           |          |
 vendor                  | character varying(255)      |           |          |
 name                    | character varying(255)      |           | not null |
 dataset_metadata        | json                        |           | not null |

我正在使用以下查询来生成预期的输出。如上面的查询输出所示。不确定如何在以下查询中使用数组和 to_timestamp 函数。是否可以使用 postgres 9.6 版本进行存档

select 
 request_id
,dataset_metadata->> 'intervals_epoch_seconds' as epoc_seconds
from fact_completeness_count_requests ;

 31319ad1-e848-4ec3-9c3e-967981e2ae45-0  | [[1725048000, 1725051600]]
 7a05cc38-5303-417d-88ce-fe3a604570d2    | [[1725055200, 1725056100]]
 ae6c2b09-8a95-4ac0-9846-6e76071579af    | [[1725050700, 1725051600], [1725049800, 1725050700], [1725048900, 1725049800], [1725048000, 1725048900]]
postgresql

1 个回答

  1. Best Answer

    unnest()返回数组的元素，无论嵌套级别如何。JSON 数组没有直接对应项，即使在当前 Postgres 版本中也没有（撰写本文时为 Postgres 16）。

    实现目标的一个巧妙的方法是将给定的二维 JSON 数组转换为 Postgres 数组：

    SELECT request_id
     , to_timestamp(unnest(translate(dataset_metadata ->> 'intervals_epoch_seconds', '[]', '{}')::float[]))
FROM   fact_completeness_count_requests;

    小提琴

    我将 JSON 数组分隔符转换[]为 Postgres 数组分隔符{}。
    如果 JSON 输入与给定格式不匹配，则有很多方法可以破坏此操作。

    有关的：

    此外，古老的 Postgres 9.6 还有更多方法让您感到困惑。请参阅：

    • 1

相关问题