PostgreSQL query nested object in array by WHERE

Question

Using PostgreSQL 13.4, I have a query like this, which is used for a GraphQL endpoint:

export const getPlans = async (filter: {
  offset: number;
  limit: number;
  search: string;
}): Promise<SearchResult<Plan>> => {
  const query = sql`
    SELECT
      COUNT(p.*) OVER() AS total_count, 
      p.json, TO_CHAR(MAX(pp.published_at) AT TIME ZONE 'JST', 'YYYY-MM-DD HH24:MI') as last_published_at 
    FROM
      plans_json p 
    LEFT JOIN
      published_plans pp ON p.plan_id = pp.plan_id
    WHERE
      1 = 1
  `;

  if (filter.search)
    query.append(sql`
    AND
      (
        p.plan_id::text ILIKE ${`${filter.search}%`}
        OR
        p.json->>'name' ILIKE ${`%${filter.search}%`}
        **OR
        p.json->'activities'->'venue'->>'name' ILIKE ${`%${filter.search}%`}
      )
  `);
  // The above OR line or this alternative didn't work
  // p @> '{"activities":[{"venue":{"name":${`%${filter.search}`}}}]}'
 .
 .
 .
}

The data I'm accessing looks like this:

{
  "data": {
    "plans": {
      "records": [
        {
          "id": "345sdfsdf",
          "name": "test1",
          "activities": [{...},{...}]
        },
        {
          "id": "abc123",
          "name": "test2",
          "activities": [
            {
              "name": "test2",
              "id": "123abc",
              "venue": {
                "name": *"I WANT THIS VALUE"* <------------------------
              }
            }
          ]
        }
      ]
    }
  }
}

~~Since the search parameter provided to this query varies, I can only make changes in the WHERE block, in order to avoid affecting the other two working searches.~~

I tried 2 approaches (see above query), but neither worked.

Using TypeORM would be an alternative.

EDIT: For example, could I make that statement work somehow? I want to compare VALUE with the search string that is provided as argument:

p.json ->> '{"activities":[{"venue":{"name": VALUE}}]}' ILIKE ${`%${filter.search}`}

In your json example, the value associated to the key "records" is an array of json objects, so what is the criteria to select the rigtht object in this array and whose '{activities, name}' value is to be selected ??? — Edouard
– Edouard, Commented Oct 27, 2021 at 14:59
Using PostgreSQL 8.5.1. I doubt that. :) Try SELECT version(); — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 27, 2021 at 23:08
@EdouardH. Yes, records is an array of objects. Those objects have a key "activities", which is another array of objects. Those objects have a key "venue", with the value of an object, that has the key "name". If this "name" matches the search parameter provided to the query via the user interface, I want to return that whole record (later it is filtered through GraphQL). The search parameter is the argument "filter.search" — GoWithTheFlow
– GoWithTheFlow, Commented Oct 28, 2021 at 0:40
@ErwinBrandstetter Oh sorry, that was the client version. The DB is actually 13.4 — GoWithTheFlow
– GoWithTheFlow, Commented Oct 28, 2021 at 0:54

Edouard · Accepted Answer · 2021-10-28 07:25:23Z

1

First, you should use the jsonb type instead of the json type in postgres for many reasons, see the manual :

... In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys...

Then you can use the following query to get the whole json data based on the search_parameter provided to the query via the user interface as far as the search_parameter is a regular expression (see the manual) :

SELECT query
  FROM plans_json p 
 CROSS JOIN LATERAL jsonb_path_query(p.json :: jsonb , FORMAT('$ ? (@.data.plans.records[*].activities[*].venue.name like_regex "%s")', search_parameter) :: jsonpath) AS query

If you need to retrieve part of the json data only, then you transfer in the jsonb_path_query function the corresponding part of the jsonpath which is in the '(@.jsonpath)' section to the '$' section. For instance, if you just want to retrieve the jsonb object {"name": "test2", ...} then the query is :

SELECT query
  FROM plans_json p 
 CROSS JOIN LATERAL jsonb_path_query(p.json :: jsonb , FORMAT('$.data.plans.records[*].activities[*] ? (@.venue.name like_regex "%s")', search_parameter) :: jsonpath) AS query

answered Oct 28, 2021 at 7:25

Edouard

7,1291 gold badge11 silver badges24 bronze badges

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

1 Comment

GoWithTheFlow Over a year ago

Thank you, I would be fine with temporarily casting to jsonb format, although in the mid term I'd probably have to convince my team to change to jsonb in the DB, to reduce performance hits. Unfortunately, it returns empty when I run the first version of the query you suggested, with the difference of replacing search_parameter with a sample string I know exists. I also tried with "like" instead of "like_regex", but this syntax doesn't seem to work here

Collectives™ on Stack Overflow

PostgreSQL query nested object in array by WHERE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related