PostgreSQL: Compare JSON Arrays

When you’re working with JSON data that contains arrays—like skill sets for employees, permissions for roles, or tags for posts—you often need to detect when those arrays have changed. Have the skills been updated? Do two users have the same permissions? It sounds simple until you realize that order might matter (or might not), and comparison becomes more nuanced.

PostgreSQL gives you multiple ways to compare JSON arrays, from simple equality checks to complex logic that ignores order and focuses on content. Knowing the difference between exact equality and content equality is crucial for building accurate business logic.

Quick Answer: Use = Operator for Exact Match

To compare JSON arrays for equality:

SELECT
    emp_id,
    first_name,
    original_skills,
    updated_skills,
    original_skills = updated_skills as skills_unchanged,
    CASE
        WHEN original_skills = updated_skills THEN 'No changes'
        ELSE 'Skills updated'
    END as change_status
FROM employees
WHERE original_skills IS NOT NULL
LIMIT 5;

Returns:

emp_id | first_name | original_skills      | updated_skills       | skills_unchanged | change_status
-------+------------+----------------------+----------------------+------------------+----------------
     1 | James      | ["Python","Java"]    | ["Python","Java"]    | t                | No changes
     2 | Mary       | ["Java","Go"]        | ["Go","Java"]        | f                | Skills updated

The = operator checks exact equality, including order.

How It Works

The = operator compares JSONB values directly. Two arrays are equal only if they have the same elements in the same order. If order doesn’t matter, you need additional logic.

Different Approaches & When to Use Them

Approach 1: Exact Equality (Order Matters)

SELECT
    array1,
    array2,
    array1 = array2 as exactly_equal
FROM table_name;

Best for: Detecting exact changes.

Approach 2: Ignore Order (Set Equality)

SELECT
    array1,
    array2,
    jsonb_array_elements(array1) = ANY(SELECT jsonb_array_elements(array2)) as same_elements_any_order
FROM table_name;

More complex but ignores order.

Best for: Comparing skill sets where order doesn’t matter.

Approach 3: Element Count Comparison

SELECT
    array1,
    array2,
    jsonb_array_length(array1) = jsonb_array_length(array2) as same_length,
    CASE
        WHEN array1 = array2 THEN 'Exactly equal'
        WHEN jsonb_array_length(array1) != jsonb_array_length(array2) THEN 'Different length'
        ELSE 'Same length, different order'
    END as comparison
FROM table_name;

Categorize differences.

Approach 4: Find Differences

SELECT
    emp_id,
    original,
    updated,
    (
        SELECT jsonb_agg(elem)
        FROM jsonb_array_elements(original) elem
        WHERE NOT (elem = ANY(SELECT jsonb_array_elements(updated)))
    ) as removed_items,
    (
        SELECT jsonb_agg(elem)
        FROM jsonb_array_elements(updated) elem
        WHERE NOT (elem = ANY(SELECT jsonb_array_elements(original)))
    ) as added_items
FROM audit_log;

Best for: Seeing exactly what changed.

Real-World Example: Data Validation

Check if expected and actual results match:

SELECT
    test_id,
    test_name,
    expected_output,
    actual_output,
    expected_output = actual_output as test_passed,
    CASE
        WHEN expected_output = actual_output THEN '✓ PASS'
        ELSE '✗ FAIL'
    END as result
FROM test_results
ORDER BY test_passed DESC;

Performance Comparison

Method Speed Use Case
Direct = operator Very Fast Exact equality
Set comparison Slower Order-independent
Element counting Medium Quick length check

Direct equality is fastest.

FAQ

Q: Does JSON array comparison care about order? A: Yes, ["A","B"] ≠ ["B","A"]. For unordered comparison, expand with UNNEST.

Q: How do I check if one array is a subset of another? A: Use @<: array1 <@ array2 checks if all elements of array1 exist in array2.

Q: Can I compare NULL elements? A: Yes, NULL = NULL is treated as equal in JSONB comparisons.

Q: Performance for large arrays? A: Direct comparison is O(1) on array size. Fast.

Q: How do I find common elements between arrays? A: Use INTERSECT or filter: array_agg(elem) WHERE elem = ANY(SELECT jsonb_array_elements(other_array)).

Wrapping Up

Compare JSON arrays with the = operator for exact equality (including order). For unordered comparison, expand arrays and use set operations. The key depends on whether order matters to your use case.