Is there a way to query an array of JSON objects returned by a CTE in PostgreSQL?-CodePudding

I have a PostgreSQL query that uses a CTE and the SELECT within the CTE uses json_agg() to aggregate data as JSON objects. Is there a way to query the results of the CTE by searching for a specific object in the array based on the value of a field of objects?

For example, lets say the CTE creates a temporary table named results. The values from the json_agg() is available in a field called owners, and each owner object has a field called name. I want to SELECT * FROM results WHERE owner.name = 'John Smith'. I am not sure how to write the WHERE clause below so that the name field of each object in the array of owners is checked for the value.

WITH results AS (
  -- some other fields here
  (SELECT json_agg(owners)
    FROM (
      SELECT id, name, telephone, email
      FROM owner
    ) owners   
  ) as owners
)
SELECT * 
FROM results 
WHERE owners->>'name' == 'John Smith'

CodePudding user response：

To do that query you can typically use the jsonpath language after having converted your json data into jsonb data (see the manual here and here) :

WITH results AS (
  -- some other fields here
  (SELECT json_agg(owners)
    FROM (
      SELECT id, name, telephone, email
      FROM owner
    ) owners   
  ) as owners
)
SELECT * 
FROM results 
WHERE jsonb_path_exists(owners :: jsonb, '$[*] ? (@.name == "John Smith")')