SQL Searching Query inside JSON object array

Question

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

In my table I have many records which contains json like this and i want a query to give me only { "startdt": "10/14/2021", "enddt": "10/14/2022", "customerName1": "Jacob", "customerName2": "NJ" } but now it the giving me the whole object. I want to search in an array seperately not the whole list — Jyoti Panjwani
– Jyoti Panjwani, Commented Sep 17, 2022 at 11:57
So just the (one) array element where customerName1 matches? What if you have multiple array elements with Jacob — user330315
– user330315, Commented Sep 17, 2022 at 12:06
If multiple array elements then all those having Jacob will be the output — Jyoti Panjwani
– Jyoti Panjwani, Commented Sep 17, 2022 at 12:09

score 3 · Accepted Answer · 2022-09-17 12:24:01Z

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

I am using postgressql and it says jsonb_path_query_array(jsonb, unknown) does not exists?
@JyotiPanjwani: then you are using an older Postgres version.
I dont want to display a new column here just want to search. How can we modify this to do it?

Collectives™ on Stack Overflow

SQL Searching Query inside JSON object array

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related