CODEWITHSUNDEEP
sqljsonpostgresql
user565
user565

Reputation: 1001

How to use other table JSON data in select query

I am using postgres and have 2 tables, deviceTble has the following columns: deviceName, device_id, type, deviceOwnerPerson_id, deviceAccessPerson_id.

The other table is Person_kv and has 2 columns id,data (containing person info but in JSON format).

I want to a select query from deviceTble and want to use first_name and last_name of a person which are in Person_kv table by given of deviceOwnerPersonId and deviceAccessPersonId.

Here is what I have to get data from person_kv table to get data in tabular form:

select data :: json ->> 'id' as id
  , data :: json ->> 'name' as first_name
  , data :: json ->> 'surename' as last_name
from Person_kv

and expected deviceTble query:

select deviceName,device_id,type from deviceTble

I am confused either I use WITH clause on person_kv query and then join here or one by one on deviceOwnerPerson_id and deviceAccessPerson_id OR is there any other way as well by using inner query

Can someone tell me how I can get required result?

Upvotes: 0

Views: 776

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51569

from you description you can just join em:

select deviceName,device_id,type, p.data:: json ->>'name' , p.data:: json ->>'surname' 
from deviceTble d
join Person_kv  p on p.data:: json ->>'id' = deviceOwnerPerson_id::text OR p.data:: json ->>'id' = deviceAccessPerson_id::text

Upvotes: 1

Related Questions