PostgreSQL替代Oracle JSON_EXISTS实现JSON条件查询方案咨询

Since you're transitioning from Oracle's JSON_EXISTS to PostgreSQL, here's how to replicate your WHERE clause based on your JSON array structure stored in a json column:

Option 1: Using PostgreSQL JSONPath (Version 12+) #

PostgreSQL 12 introduced JSONPath support, which aligns closely with Oracle's JSON query syntax. Use the @? operator to check if any element in the JSON array matches your conditions:

WHERE 
  -- Check if punchEntry.entrySource.clock is true in any array element
  client_policy_js @? '$[*].inputs.supportedEntryTypes.punchEntry.entrySource.clock == true'
  OR
  -- Check if timeSheetEntry exists in any array element
  client_policy_js @? '$[*].inputs.supportedEntryTypes.timeSheetEntry exists()'

Breakdown: #

• $[*]: Iterates over all elements in the root JSON array
• @?: Tests if the JSONPath expression returns at least one matching value
• exists(): Verifies the specified path exists (equivalent to Oracle's check for the timeSheetEntry key)

Option 2: For Older PostgreSQL Versions (Pre-12) #

If you're on a version before 12, use json_array_elements to unnest the array and check each element with PostgreSQL's native JSON operators:

WHERE
  -- Condition 1: punchEntry.entrySource.clock equals true
  EXISTS (
    SELECT 1 
    FROM json_array_elements(client_policy_js) AS elem
    WHERE (elem -> 'inputs' -> 'supportedEntryTypes' -> 'punchEntry' -> 'entrySource' ->> 'clock')::boolean = true
  )
  OR
  -- Condition 2: timeSheetEntry exists as a key
  EXISTS (
    SELECT 1 
    FROM json_array_elements(client_policy_js) AS elem
    WHERE elem -> 'inputs' -> 'supportedEntryTypes' ? 'timeSheetEntry'
  )

Breakdown: #

• json_array_elements(client_policy_js): Unnests the JSON array into rows of individual objects
• ->: Navigates to a JSON object/value (returns json type)
• ->>: Extracts a JSON value as text (used here to cast the clock boolean to a usable type)
• ?: Checks if a key exists in a JSON object (ideal for verifying timeSheetEntry is present)

Both options will filter rows where either of your original conditions is met, matching the behavior of your Oracle query.

内容的提问来源于stack exchange，提问作者pavan kumar atluri