PostgreSQL – How to Query a JSON Type Column

jsonpostgresql

I have a table in Postgres and trying to query a JSON type columns data_meta & source_data

request_id               | bbb1-bdfa3ae765a2
job_type                 | Incoming
data_meta                | {"data_type": "Insurance", "technology": "XYZ", "timestamp_column_name": "JOBTIME"}
source_data              | [{"vendor": "ABCD", "name": "MAPPING_FILE", "data_type": "Insurance"}]
status                   | OK


SELECT * FROM   job_request_table p, json_array_elements(p.data_meta) as element
where element->'data_type' ='Insurance';

and getting this:

ERROR:  operator does not exist: json = unknown
LINE 2: where element->'data_type' ='Insurance';
                                   ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

will there be a change in query if I have to search for vendor='ABCD' in another JSON type column "source_data"

Best Answer

You need to extract the value as text which is done using the ->> operator (-> returns a json value).

SELECT * 
FROM job_request_table p
  cross join json_array_elements(p.data_meta) as d(element)
where d.element ->> 'data_type' = 'Insurance';

However, given your sample data, this would result in the error "cannot extract elements from an object" as the content of data_meta is not an array.

If you want to test if a JSON value contains a specific key/value pair, use @> - but that can only be used with jsonb, so you need to cast the column:

SELECT * 
FROM job_request_table p
where data_meta::jsonb @> '{"data_type": "Insurance"}';

You can use this also to check inside the array in source_data (but again a cast to jsonb is neeed):

where source_data::jsonb @> '[{"vendor": "ABC"}]'

In the long run, I would recommend to change both columns to jsonb

Related Question