CODEWITHSUNDEEP
jsonpostgresql
JSONB
JSONB

Reputation: 31

Join a json value with a column in postgresql

I want to join a column with a json value. The problem is the json value is within square brackets and its a uuid. Table name that has the json column(column named json) is department and the other table name is staff. The json column value would be like below,

{"title":"Manager","alternativeTitle":null,"departmentIds":["c8098u43-7d9a-3789-gt56-r78009v4r345"]}

I would like to query the departmentIds from the json column and join it with staffdepartmentID column in the staff table.

My query for the join

from staff s
join department d on d.json ->> departmentIds::json = s.staffdepartmentID

The problem I am facing is that I dont know how to remove those square brackets. Any help is highly appreciated. Thanks

Upvotes: 0

Views: 170

Answers (1)

Edouard
Edouard

Reputation: 7065

Square braquets within a json data correspond to an array. You can access any element of the array based on its position starting with 0 for the first element : array->0

So for your query you can do :

from staff s
inner join department d
on d.json -> 'departmentIds'->>0 = s.staffdepartmentID :: text

Upvotes: 1

Related Questions