时间戳

首页 > 解决方案 > Postgresql json_each equivalent in Presto

postgresql - Postgresql json_each equivalent in Presto

问题描述

I have data which I used to extract from Postgresql using json_each(x), I am looking for an equivalent of json_each in Presto after a database migration to extract my data into multiple rows. The original format is as such {"0":100.0,"57000":75.0,"47000":80.0}

Expected Output to separate key and values into 2 columns of multiple rows

Any idea?

标签： postgresqlpresto

解决方案

这可以通过将Trino（以前称为 PrestoSQL）JSON 函数与UNNEST. 步骤是：

  1. 将原始 JSON 解析为 JSON 类型json_parse
  2. 使用 JSON 转换表达式从 JSON 转换为地图
  3. 使用 SQL 标准UNNEST表函数将地图取消嵌套到行中

这是您的示例数据的查询：

WITH t AS (SELECT '{"0":100.0,"57000":75.0,"47000":80.0}' AS raw_json)
SELECT key, value
FROM
    t,
    UNNEST(
        CAST(json_parse(raw_json) AS map(integer, double))
    ) x(key, value)

为了将来参考，这里描述了所有功能：https ://trino.io/docs/current/functions.html

推荐阅读