PostgreSQL query nested object in array by WHERE-CodePudding

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}`}

CodePudding user response：

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