Postgres:连接ID上嵌套在JSON中的表

Question

我在postgres中创建了两个表，每个表包含两个列、一个uuid列和一个包含JSON的列。

表A(省)：

第1栏(ProvinceID)：

"112233“

第2栏(JSON)：

{
    "id": "123",
    "name":"Ontario",
    "cities": [
     "456",
     "789",
     "112",
     "141"
    ],
    "status": 0
}

表B(城市)：

第1栏(CityId)：

"465“

第2栏(JSON)：

{
    "id": "456",
    "name":"Toronto",
    "NHL Team": 1
    "Population": 4000000
}

在这个例子中，一个省与城市有一对多的关系。到目前为止，我没有做到的是将表A(省)中的一个省加入到表B(城市)中的所有城市。我没有这样做，因为CityId作为嵌套字段存储在表A(省)中。

例如：

FROM Provinces JOIN Cities on (Provinces.JSON->'Cities' = Cities.CityId)

我试验了内置的JSON函数#>、->、json_array_elements --我似乎找不到返回正确输出的解决方案。

任何帮助都是非常感谢的。

Answer 1

select *
from
    (
        select
            id, name,
            json_array_elements(JSON -> 'cities')::text::int as CityId,
            status
        from provinces
    ) provinces p
    inner join
    Cities c using (CityId)

在已发布的JSON数据中，cities属性的大小写为首字母。如果它有第一个大写字母，那么更改上面的代码。

如果Cities表中的text类型为text，则不需要将JSON -> 'cities'转换为integer。

顺便说一句，这看起来像糟糕的数据库设计。JSON并不能代替好的设计。