CODEWITHSUNDEEP
jsonpostgresql
jmorrison
jmorrison

Reputation: 191

Postgres: Joining Tables on ID nested within JSON

I have created 2 tables in my postgres db, each contains two columns, a uuid column, and a column that contains JSON.

Table A (Provinces):

Column 1 (ProvinceID):

"112233"

Column 2 (JSON):

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

Table B (Cities):

Column 1 (CityId):

"465"

Column 2 (JSON):

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

In this example, a province has a one to many relationship with cities. What I've failed to do so far is join a province in table A (Provinces) to all of its cities in table B (Cities). I'm failing to do so because the CityId is stored as a nested field in Table A (Provinces).

e.g.

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

I've experimented with the built in JSON functions #>, ->, json_array_elements - I can't seem to find a solution that returns correct output.

Any help is appreciated.

Upvotes: 1

Views: 1602

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125374

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)

In the posted JSON data the cities property has a lower case first letter. If it has a first capital letter then change the code above.

If CityId in the Cities table is of type text then there is no need to cast JSON -> 'cities' to integer

BTW that looks like bad database design. JSON is not a substitute for good design.

Upvotes: 3

Related Questions