PostgreSQL: JSONB

PostgreSQL Official document: JSON Functions and Operators

Gets all JSON items returned by JSON path for the specified JSON value
and wraps result into an array.

SELECT id, jsonb_path_query_array(payload::jsonb, '$.commits[*].id')
FROM github_events
WHERE id = '1234567';

Returns the result of JSON path predicate check for the specified JSON value.
Only the first item of the result is taken into account.
If the result is not Boolean, then null is returned.

SELECT id, payload
FROM github_events
WHERE project_id = 1
  AND jsonb_path_match(payload::jsonb, 'exists($.commits[*] ? (@.id == "1234567"))')
LIMIT 1;