0

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"

1 Answer 1

3

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

1
  • Thanks. working as expected Commented Nov 24, 2020 at 6:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.