jsonb LIKE query on nested objects in an array

Question

My JSON data looks like this:

[{
  "id": 1,
  "payload": {
    "location": "NY",
    "details": [{
            "name": "cafe",
            "cuisine": "mexican"
        },
        {
            "name": "foody",
            "cuisine": "italian"
        }
    ]
  }
}, {
  "id": 2,
  "payload": {
    "location": "NY",
    "details": [{
            "name": "mbar",
            "cuisine": "mexican"
        },
        {
            "name": "fdy",
            "cuisine": "italian"
        }
    ]
  }
}]

given a text "foo" I want to return all the tuples that have this substring. But I cannot figure out how to write the query for the same.

I followed this related answer but cannot figure out how to do LIKE.
This is what I have working right now:

SELECT r.res->>'name' AS feature_name, d.details::text
  FROM   restaurants r
    , LATERAL (SELECT ARRAY (
     SELECT * FROM json_populate_recordset(null::foo, r.res#>'{payload, 
       details}')
     )
   ) AS d(details)
 WHERE d.details @> '{cafe}';

Instead of passing the whole text of cafe I want to pass ca and get the results that match that text.

Erwin Brandstetter · Accepted Answer · 2022-06-19 01:48:30Z

1

Your solution can be simplified some more:

SELECT r.res->>'name' AS feature_name, d.name AS detail_name
FROM   restaurants r
     , jsonb_populate_recordset(null::foo, r.res #> '{payload, details}') d
WHERE  d.name LIKE '%oh%';

Or simpler, yet, with jsonb_array_elements() since you don't actually need the row type (foo) at all in this example:

SELECT r.res->>'name' AS feature_name, d->>'name' AS detail_name
FROM   restaurants r
     , jsonb_array_elements(r.res #> '{payload, details}') d
WHERE  d->>'name' LIKE '%oh%';

db<>fiddle here

But that's not what you asked exactly:

I want to return all the tuples that have this substring.

You are returning all JSON array elements (0-n per base table row), where one particular key ('{payload,details,*,name}') matches (case-sensitively).

And your original question had a nested JSON array on top of this. You removed the outer array for this solution - I did the same.

Depending on your actual requirements the new text search capability of Postgres 10 might be useful.

edited Jun 19, 2022 at 1:48

answered Jun 23, 2017 at 13:47

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

suprita shankar Over a year ago

Yes correct. But I have the ids, name of the top level tuple. This is what I am going to return(the ids that have the substr). Thanks for the observation though.

suprita shankar · Accepted Answer · 2017-06-23 07:01:55Z

1

I ended up doing this(inspired by this answer - jsonb query with nested objects in an array)

SELECT r.res->>'name' AS feature_name, d.details::text
FROM   restaurants r
 , LATERAL  (
     SELECT * FROM json_populate_recordset(null::foo, r.res#>'{payload, details}')
   ) AS d(details)
WHERE d.details LIKE '%oh%';

Fiddle here - http://sqlfiddle.com/#!15/f2027/5

answered Jun 23, 2017 at 7:01

suprita shankar

1,5792 gold badges19 silver badges50 bronze badges

Collectives™ on Stack Overflow

jsonb LIKE query on nested objects in an array

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related