I have two tables and writing a query by taking data from both the tables as below:
select distinct on (e.pol) e.pol, ei,bene, ei.status,
(jsonb_array_elements(ei.name_json)->> 'custName1') as name1,
(jsonb_array_elements(ei.name_json)->> 'custName1') as name2
from table1 e, table2 ei
cross join (jsonb_array_elements(ei.name_json)
where e.pol=ei.pol and value->>'custName1' like '%Tes%'
order by e.pol, ei.bene
In this query I am trying to search from a json array "name_json" as follows:
[
{
"custName1": "Tesla",
"custName2": ""
},
{
"custName1": "Gerber",
"custName2": "N"
}
]
I am displaying only that column which is having the distinct policy and its respective custName1 and wantto search for only field that is diaplayed in my output. How to do it? How to search only on the columns displayed in the output?
Help is appreciated.