PostgreSQL JSON field as a query criteria case

Business extension fields are often stored in the database using JSON-formatted data, which involves a headache, assuming that a value in the extension field should be used as a query condition, and PostgreSQL itself supports this query.

Example: Suppose the Business extension field ext_data the JSON format as follows:

We need to query all the data extinfo.usertype=1 in the extended field, then the corresponding SQL statement is as follows:

Select *  from where (ext_data::json#>>'{extinfo,usertype}')::text= ' 1 '

PostgreSQL JSON field as a query criteria case