PostgreSQL - query for array elements inside json data type

My application has a table column "params" that is a JSON data type.  As a side note, it's a JSON data type (vs JSONB) because the ordering of keys must stay consistent because this column is hashed and used as the key for caching.  The contents of the "params" column has object values that are strings, numbers, and arrays.

Environment: 

PostgreSQL 12.9

Take a params value that looks like:

{
    "filterId": "a1cef72a-9d84-4cfc-9690-9f4d772f446c",
    "name": "cherryshoetech",
    "active": true,
    "priority": 2,
    "areas": [{
            "type": "custom",
            "startedInside": true,
            "endedInside": false,
            "order": 0
        }
    ],
}

To query for a specific value in the "areas" array, make use of the json_array_elements function.  It will expand a JSON array to a set of JSON values.  Therefore, it will return one record for each element in the array.

The following will return a record for each json array element in analysis.params.areas:

select id, created_tstamp, cherryshoeareas
from cherryshoe.analysis analysis, json_array_elements(analysis.params#>'{areas}') cherryshoeareas;

Once you filter it down with the where clause, it will only return the record that satisfies that criteria. Below are examples for filtering by a string, integer, and boolean:

select id, created_tstamp, cherryshoeareas
from cherryshoe.analysis analysis, json_array_elements(analysis.params#>'{areas}') cherryshoeareas
where cherryshoeareas ->> 'type' = 'custom';

select id, created_tstamp, cherryshoeareas
from cherryshoe.analysis analysis, json_array_elements(analysis.params#>'{areas}') cherryshoeareas
where (cherryshoeareas ->> 'order')::integer = 0;

select id, created_tstamp, cherryshoeareas
from cherryshoe.analysis analysis, json_array_elements(analysis.params#>'{areas}') cherryshoeareas
where (cherryshoeareas ->> 'startedInside')::boolean is true
and (cherryshoeareas ->> 'endedInside')::boolean is false;

Helpful Articles:

https://www.postgresql.org/docs/12/datatype-json.html

https://stackoverflow.com/questions/22736742/query-for-array-elements-inside-json-type

https://www.postgresql.org/docs/12/functions-json.html