Sql 将 json 列键映射到第二个表中的匹配列值

发布于09月01日

我有一个表A，其中data是json列，timestamp是带有时区列的时间戳:

timestamp	data
2023-08-29 13:00:00-04	{ "a_123":{ "temp":85, "uv":5, "rain":0 }, "b_123":{ "temp":85, "uv":5, "rain":0 } }
2023-08-29 14:00:00-04	{ "a_123":{ "temp":70, "uv" 1:, 1"rain":5 }, "b_123":{ "temp":73, "uv":1, "rain":7 } }
2023-08-29 15:00:00-04	{ "a_123":{ "temp":83, "uv":4, "rain":1 }, "b_123":{ "temp":87, "uv":7, "rain":0 } }

我还有第二张桌子B:

id	location	elevation	tag
a_123	04662	155m	blue
b_123	84003	15m	yellow

我可以映射单独的数据列键，例如.a_123从表A复制到来自表B的匹配id，但在我的例子中，来自表A的数据列键是动态的.

如何从上面提供的样例数据生成一个表，其中嵌套的时间戳数据映射到每个id，如下所示？感谢您的帮助/文档.

id	location	elevation	tag	data
a_123	04662	155m	blue	{ "2023-08-29 13:00:00-04":{ "temp":85, "uv":5, "rain":0 }, "2023-08-29 14:00:00-04":{ "temp":70, "uv":1, "rain":5 }, "2023-08-29 15:00:00-04":{ "temp":83, "uv":4, "rain":1 } }
b123	84003	15m	yellow	{ "2023-08-29 13:00:00-04":{ "temp":85, "uv":5, "rain":0 }, "2023-08-29 14:00:00-04":{ "temp":73, "uv":1, "rain":7 }, "2023-08-29 15:00:00-04":{ "temp":87, "uv":7, "rain":0 } }

select t.id, t.location, t.elevation, t.tag, jsonb_object_agg(t1.timestamp, v.value) from tableb t cross join tablea t1 cross join jsonb_each(t1.data) v where t.id = v.key group by t.id, t.location, t.elevation, t.tag

Sql 将 json 列键映射到第二个表中的匹配列值

推荐答案

Sql相关问答推荐

如何解决Error：operator is not unique：unknown—unknown在一个动态SQL查询？""""

SQL Google Sheets：UNIQUE/DISTINCT和编码查询函数

用于平均多个数据并与一个数据点进行比较以判断偏移量的SQL查询

如何在Snowflake SQL存储过程中传递LIMIT和OFFSET的参数？

在postgres中动态计算出现次数并插入到json中

R中对Arrow duckdb工作流的SQL查询

Access 365将文本转换回BigInt

不同表达方式时的大小写

将所有XML文件导入到SQL Server中

在SELECT中将日期格式转换为双周时段

SQL递归.硬币兑换问题.-try 使用递归解决硬币找零问题

仅当 SQL Server 中的表为开时，才在存储过程中使用更改跟踪

将空 JSON 数组添加到 SQL Server 表列中的 JSON 字符串

如何从postgresql中的项目映射(关联数组)设置值？

SQL获取两个日期范围之间的计数

忽略与给定列匹配的行的 LAG 函数

Oracle SQL 从多个条件中 Select 但具有相同的 id

根据条件列出不同的值

BigQuery - 将 TIMESTAMP 转换为 HH：MM：SS，然后识别 TIME_DIFF

BigQuery 将一行拆分为多列