I want to query the value for JSON field role, which will always be inside the last JSON element in the column ip in the table named employeeRole. The whole JSON data for that column would be like below,

[{"submitterNumber":null,"name":{"fullName":"*****","parts":null},"role":"developer:56987t63-y876-3678","department":"****","Code":null,"pin":null,"Nnis":null},{"submitterNumber":null,"name":{"fullName":"******","parts":null},"role":"approver:38675r40-i456-0934","department":"****","Code":null,"ipn":null,"Nnis":null},{"submitterNumber":null,"name":{"fullName":"*****","parts":null},"role":"tester:76590w23-o895-3045","department":null,""Code":"****","ipn":null,"isni":null}]

I want to query only the guid(i.e the 56987t63-y876-3678) part of this field "role":"developer:56987t63-y876-3678" from the above JSON element.

Each row for the column IP would have a very similar JSON element as above. But the problem is not all row would have the final JSON field {"submitterNumber":null,"name":{"fullName":"*****","parts":null},"role":"tester:76590w23-o895-3045","department":null,""Code":"****","ipn":null,"isni":null} in that same position. So employeeRole.ip -> 3 -> role would not work always in all scenarios, as something else would be in the position 3 in other rows. But it will always be the final JSON field.

So, I expect an answer that I will be able to use the result of this query to check with a subquery, whether the result of this query(i.e the guid value) is in the result on the subquery. If it does that's what will be queried. The subquery part is done, but I am not sure how to the query the above JSON field and join in with the subquery.

Any help is highly appreciated. Thanks.

CodePudding user response：

The -> operator you're using also supports relative indexing:

Extracts n'th element of JSON array (array elements are indexed from zero, but negative integers count from the end).

So to get the role of the last object in the array, just write

employeeRole.ip -> -1 ->> role