在 PG10 数据库中,我有一个名为的表ToDos
,我在其中定义了一个jsonb
名为tags
.
记录看起来像
+----+-----------------+-----------------------------------------+
| ID | Name | Tags |
+----------------------+-----------------------------------------+
| 1 | mow the lawn |[{"name": "Saturday", "scope": "system"},|
| | | {"name": "favorite", "scope": "user"}] |
+----------------------+-----------------------------------------+
| 2 | paint the fence |[{"name": "Monday", "scope": "system"}] |
+----------------------+-----------------------------------------+
现在我正在尝试编写一个返回类似内容的查询
+----+-----------------+------------+-----------+
| ID | Name | Tag Name | Tag Scope |
+----------------------+------------+-----------+
| 1 | mow the lawn | Saturday | system |
+----------------------+------------+-----------+
| 1 | mow the lawn | favorite | user |
+----------------------+------------+-----------+
| 2 | paint the fence | Monday | system |
+----------------------+------------+-----------+
但我很难分解这个jsonb
领域。有任何想法吗?
您需要取消嵌套数组,然后从生成的 JSON 值中将键提取为列:
在线示例:http ://rextester.com/DWYIJ81620