Tibco and postgres work with Json and Jsonb datatypes

Postgres database allows to insert and query json documents. To insert a json or jsonb object from tibco 5 we need to type cast and insert the object.

Have a look at the followin here we are casting two json string to json and jsonb data types columns.

The insert query is as following

INSERT INTO vehiclelocation.locations
VALUES(?,?::json,?::jsonb)

Now the json document can be queried as following.

Following is the select query where we query the busid and busserial from a json array of vehicle store in a table called locations.

select elem->>'bus_id' as busid, elem->>'bus_serial' as busserial
from vehiclelocation.locations t,
json_array_elements(t."location" ->'Vehicle') elem
where t.company_id = 26 and elem->>'bus_id' = '1'