NSDT工具推荐： Three.js AI纹理开发包 - YOLO合成数据生成器 - GLTF/GLB在线编辑 - 3D模型格式在线转换 - 可编程3D场景编辑器 - REVIT导出3D模型插件 - 3D模型语义搜索引擎 - Three.js虚拟轴心开发包 - 3D模型在线减面 - STL模型在线切割
开发人员喜欢使用 JSON，当他们存储数据时，通常不会对其进行规范化。 因此，在数据库中遇到 JSON(b) 字段并不罕见。 对于数据分析师、数据科学家和机器学习工程师来说，取消嵌套或扁平化，数据往往是后续分析的先决条件。
整理数据库中的 JSON 列可能具有挑战性。 通常目标是将 JSON 数组扩展到新行并将 JSON 对象取消嵌套到新列中。 幸运的是，Postgres 有几个内置函数，可以组合这些函数来处理数据库中复杂 JSON 的规范化。
1、数据集
下表说明了一个常见的场景，其中一个表（我们将其称为城市）包含一个 JSON 或 JSON(b) 列（即坐标）。
+---+-------------------+-------+---------------------------------+
|idx|name |country|coordinates |
+---+-------------------+-------+---------------------------------+
|1 |Sant Julià de Lòria|AD |{"lat": 42.46372, "lng": 1.49129}|
|2 |Pas de la Casa |AD |{"lat": 42.54277, "lng": 1.73361}|
|3 |Ordino |AD |{"lat": 42.55623, "lng": 1.53319}|
|4 |les Escaldes |AD |{"lat": 42.50729, "lng": 1.53414}|
|5 |la Massana |AD |{"lat": 42.54499, "lng": 1.51483}|
|6 |Encamp |AD |{"lat": 42.53474, "lng": 1.58014}|
+---+-------------------+-------+---------------------------------+虽然数据存储为 JSON 对象，但数据分析师、数据科学家或 ML 工程师更喜欢将 lat 和 lng 规范化为列。 幸运的是，我们可以使用 Postgres 内置的 jsonb_to_record 函数来构造 JSON 对象。
2、使用 jsonb_to_record 展平 JSON 对象
如果相关列包含 JSON(b) 对象，你可以使用内置函数 jsonb_to_record（或 json_to_record）将键值对规范化为列。 该函数接受一个 JSON 对象或包含 JSON 对象的列，并返回一条记录。 通过在 AS 表达式中匹配用户提供的复合类型来扩展记录。 复合类型表达式只是 JSON 对象键名（区分大小写）和用于它的 Postgres 数据类型。 JSON 对象可以包含比您在复合类型中定义的更多的键值对，它们将被排除。 如果复合类型定义了 JSON 对象中缺少的键，则它的值为 null。
Postgres 文档显示了如何在给定 JSON 对象的情况下使用 json_to_record：
SELECT *
FROM json_to_record('{"a":1,"b":[1,2,3],"c":"bar"}') AS
x(a int, b text, d text) a | b | d
---+---------+---
1 | [1,2,3] |在此示例中，该函数接收一个 JSON 对象 {"a":1,"b":[1,2,3],"c":"bar"}。 用户定义的复合类型 x 定义键 a、b 和 d 的映射。 因为 JSON 对象包含键 a 和 b，并且数据类型与复合类型中的数据类型匹配，所以它们被提取为记录的一部分。 复合类型没有为 c 定义映射，所以它不是从对象中提取的。 此外，复合类型定义了对象中缺少的 d，因此未被提取。
文档中的示例很好，但通常我们希望在表列而不是字符串中取消嵌套 JSON。 幸运的是，我们可以在包含 JSON 对象的列上使用相同的 json_to_record 和 jsonb_to_record 函数。
以上面的城市表为例，我们可以将坐标列中的 JSON 对象展平。
SELECT
city.idx,
city."name",
city.country,
coord.lat,
coord.lng
FROM
city,
jsonb_to_record(coordinates) AS coord(lat numeric, lng numeric);+---+-------------------+-------+--------+-------+
|idx|name |country|lat |lng |
+---+-------------------+-------+--------+-------+
|1 |Sant Julià de Lòria|AD |42.46372|1.49129|
|2 |Pas de la Casa |AD |42.54277|1.73361|
|3 |Ordino |AD |42.55623|1.53319|
|4 |les Escaldes |AD |42.50729|1.53414|
|5 |la Massana |AD |42.54499|1.51483|
|6 |Encamp |AD |42.53474|1.58014|
+---+-------------------+-------+--------+-------+在此示例中，我们在坐标列上使用 jsonb_to_record 将对象展平为两列。 复合类型坐标匹配对象键和数据类型。 然后，我们可以在 SELECT 语句中引用与复合类型匹配的记录，以将 lat 和 lng 作为单独的列返回。
3、使用 jsonb_to_recordset 取消嵌套 JSON 数组
如果你的数据是 JSON 数组而不是 JSON 对象，则不能使用 jsonb_to_record 对其进行规范化。 相反，您想使用相关函数 jsonb_to_recordset。
内置函数 json_to_recordset 和 jsonb_to_recordset 与对应的 json_to_record 和 jsonb_to_record 非常相似。 不同之处在于 *_to_recordset 函数对 JSON 数组而不是 JSON 对象进行操作。
为了说明这一点，请考虑下表（称为国家/地区）。 该表与上面的城市表类似，只是每个城市一行，每个国家一行。 cities 列包含一个 JSON 数组，其中每个条目都是一个城市，存储为 JSON 对象。
+---+------------+---------------------------------------------------------------+
|idx|country_name|cities |
+---+------------+---------------------------------------------------------------+
|1 |AU |[{"name": "York", "coordinates": {"lat": -31.88809, "lng": 1...|
|2 |AT |[{"name": "Neu-Guntramsdorf", "coordinates": {"lat": 48.0642...|
|3 |AR |[{"name": "Zárate", "coordinates": {"lat": -34.09814, "lng":...|
|4 |AG |[{"name": "Saint John’s", "coordinates": {"lat": 17.12096, "...|
|5 |AO |[{"name": "Saurimo", "coordinates": {"lat": -9.66078, "lng":...|
|6 |AQ |[{"name": "McMurdo Station", "coordinates": {"lat": -77.846,...|
+---+------------+---------------------------------------------------------------+在 cities 列上使用 jsonb_to_recordset 函数，我们可以将 JSON 数组扩展为单独的行。 和以前一样，我们提供了一个包含键名和数据类型的复合类型。
SELECT
idx,
country_name,
city.name,
city.coordinates
FROM country,
jsonb_to_recordset(cities) AS city(name text, coordinates jsonb);+---+------------+---------+------------------------------------+
|idx|country_name|name |coordinates |
+---+------------+---------+------------------------------------+
|1 |AU |York |{"lat": -31.88809, "lng": 116.7678} |
|1 |AU |Yanchep |{"lat": -31.54678, "lng": 115.63171}|
|1 |AU |Yallingup|{"lat": -33.64592, "lng": 115.03514}|
|1 |AU |Wundowie |{"lat": -31.76163, "lng": 116.3799} |
|1 |AU |Wooroloo |{"lat": -31.8038, "lng": 116.31311} |
|1 |AU |Woodville|{"lat": -34.88333, "lng": 138.55} |
+---+------------+---------+------------------------------------+请注意每个城市的 idx 值（国家/地区索引列）重复。
4、展平嵌套的 JSON
在前面的示例中，复合类型使用 jsonb 类型作为坐标键。 因此，查询将坐标作为 JSONB 列返回。 鉴于我们的目标是扁平化 JSON 数据，我们可以在之前的查询的基础上构建以返回单独的 lat 和 lng 列。
SELECT
idx,
country_name,
city.name,
coord.lat,
coord.lng
FROM country,
jsonb_to_recordset(cities) AS city(name text, coordinates jsonb),
jsonb_to_record(coordinates) AS coord(lat numeric, lng numeric);+---+------------+---------+---------+---------+
|idx|country_name|name |lat |lng |
+---+------------+---------+---------+---------+
|1 |AU |York |-31.88809|116.7678 |
|1 |AU |Yanchep |-31.54678|115.63171|
|1 |AU |Yallingup|-33.64592|115.03514|
|1 |AU |Wundowie |-31.76163|116.3799 |
|1 |AU |Wooroloo |-31.8038 |116.31311|
|1 |AU |Woodville|-34.88333|138.55 |
+---+------------+---------+---------+---------+同时使用 jsonb_to_recordset 和 jsonb_to_record，我们能够展平 JSON 数据，以便每个城市有一行，重复国家数据。
5、结束语
在数据库中争论 JSON 的数据分析师、数据科学家和 ML 工程师可以使用 Postgres 的内置函数来规范化数据。 通过将这些函数组合在一起，可以将复杂的 JSON 扩展为新的行和列，以便它们可以在下游用于数据探索、数据分析和构建模型。
原文链接：Flattening JSON(b) in Postgres
BimAnt翻译整理，转载请标明出处
