Handling a complex query that returns values in JSON along with data

YogiYang

Hello,

Are there any possibilities of being able to process such a query.

SELECT 
  tm.*,
  json_agg(tmm.*) AS team_members
FROM team_master_new tm
LEFT JOIN team_member_new tmm ON tm.id = tmm.team_id
WHERE (tm.country ILIKE '%' || $1 || '%' OR '%' || $1 || '%' IS NULL)
 AND (tm.state ILIKE '%' || $2 || '%' OR '%' || $2 || '%' IS NULL)
 AND (tm.area_of_expertise ILIKE '%' || $3 || '%' OR '%' || $3 || '%' IS NULL)
 AND (tm.employment_type ILIKE '%' || $4 || '%' OR '%' || $4 || '%' IS NULL)
GROUP BY tm.id
ORDER BY tm.id;

This query will return a filed which will contain data in JSON format.

Are there any features that allow handling and displaying data returned by such a query?

TIA

lsw-fabian

Queries like this can be processed within backend functions

For example this backend function get dates of paid vacations

gabriel

You can use combined & complex filters, aggregations and so on in most views/lists. If you want to handle/parse JSON, as @lsw-fabian says, there are backend functions. These are Postgres PL/SQL functions and can natively work with JSON.

If you need to, you can also prepare JSON data inside a backend function, retrieve it with a frontend function and then parse it in JavaScript to update forms/fields or do other cool stuff.