SQL Searching Query inside JSON object array-CodePudding

Consider the below JSON object

[
  {
    "startdt": "10/13/2021",
    "enddt": "10/13/2022",
    "customerName1": "John",
    "customerName2": "CA"
  },
  {
    "startdt": "10/14/2021",
    "enddt": "10/14/2022",
    "customerName1": "Jacob",
    "customerName2": "NJ"
  }
]

This is the value present in a table "CustInfo" in the column "custjson" in Postgress DB. I want to search the data for the field customerName1. I have created the below query but it is searching in the whole object in such a way that if I give customerName1 as "Jacob" it gives the whole array. I want to search only for a particular array and return the same.

SELECT DISTINCT ON(e.id) e.*, 
       (jsonb_array_elements(e.custjson)->>'customerName1') AS name1 
  FROM CustInfo e 
 CROSS JOIN jsonb_array_elements(e.custjson) ej 
 WHERE value ->> 'customerName1' LIKE '%Jacob%'

Is there a way in which we can only search the "Jacob" customerName1's array instead of whole json? For eg: if i search for Jacob i should get the following istead of searching the whole JSON

{
  "startdt": "10/14/2021",
  "enddt": "10/14/2022",
  "customerName1": "Jacob",
  "customerName2": "NJ"
}

Any help would be greatly helpful

CodePudding user response：

You can use a JSON path expression to find the array element with a matching customer name:

select e.id, 
       jsonb_path_query_array(e.custjson, '$[*] ? (@.customerName1 like_regex "Jacob")')
from custinfo e

Based on your sample data, this returns:

id | jsonb_path_query_array                                                                             
--- ----------------------------------------------------------------------------------------------------
 1 | [{"enddt": "10/14/2022", "startdt": "10/14/2021", "customerName1": "Jacob", "customerName2": "NJ"}]

If you are using an older Postgres version, that doesn't support JSON path queries, you need to unnest and aggregate manually:

select e.id, 
       (select jsonb_agg(element) 
        from jsonb_array_elements(e.custjson) as x(element)
        where x.element ->> 'customerName1' like  '%Jacob%')
from custinfo e

This assumes that custjson is defined with the data type jsonb (which it should be). If not, you need to cast it: custjson::jsonb