How to execute json query in talend job for postgres database
Hi All,
I need to execute json query in postgres database below is the example.
When i run this query in tpostsqlInput component, i get many compilation error.
I am able to execute this query in pgadmin, can some one help.
WITH aid AS (
SELECT u.eid
, btrim((u.type)::text, '"'::text) AS type
, btrim((u.value)::text, '"'::text) AS value
FROM (
SELECT xyz.eid
, (xyz.value -> 'type'::text) AS type
, (xyz.value -> 'value'::text) AS value
FROM (
WITH reports(data) AS (
SELECT (e_1.data)::json AS data
, e_1.eid
FROM entities_history e_1
WHERE e_1.version = (SELECT max(i.version) AS max FROM entities_history i WHERE i.eid = e_1.eid)
-- AND eid = 29381
-- AND bulk_load_id = '506100'
)
SELECT r.data, r.eid, obj.value
FROM reports r
, LATERAL json_array_elements((r.data #> '{alternateIds}'::text[])) obj(value)) xyz
) u
WHERE (btrim((u.type)::text, '"'::text) = 'SCID'::text)
)
, gsl6 AS (
SELECT u.eid
, btrim((u.type)::text, '"'::text) AS type
, btrim((u.value)::text, '"'::text) AS value
FROM (
SELECT xyz.eid
, (xyz.value -> 'type'::text) AS type
, (xyz.value -> 'value'::text) AS value
FROM (
WITH reports(data) AS (
SELECT (e_1.data)::json AS data
, e_1.eid
FROM entities_history e_1
WHERE e_1.version = (SELECT max(i.version) AS max FROM entities_history i WHERE i.eid = e_1.eid)
-- AND eid = 99485
-- AND bulk_load_id = '506100'
)
SELECT r.data, r.eid, obj.value
FROM reports r
, LATERAL json_array_elements((r.data #> '{alternateIds}'::text[])) obj(value)) xyz
) u
WHERE (btrim((u.type)::text, '"'::text) = 'GSL6'::text)
)
, gold_id AS (
SELECT u.eid
, btrim((u.type)::text, '"'::text) AS type
, btrim((u.value)::text, '"'::text) AS value
FROM (
SELECT xyz.eid
, (xyz.value -> 'type'::text) AS type
, (xyz.value -> 'value'::text) AS value
FROM (
WITH reports(data) AS (
SELECT (e_1.data)::json AS data
, e_1.eid
FROM entities_history e_1
WHERE e_1.version = (SELECT max(i.version) AS max FROM entities_history i WHERE i.eid = e_1.eid)
-- AND eid = 99485
-- AND bulk_load_id = '506100'
)
SELECT r.data, r.eid, obj.value
FROM reports r
, LATERAL json_array_elements((r.data #> '{alternateIds}'::text[])) obj(value)) xyz
) u
WHERE (btrim((u.type)::text, '"'::text) = 'GOLD_ID'::text)
)
, branch_id AS (
SELECT u.eid
, btrim((u.type)::text, '"'::text) AS type
, btrim((u.value)::text, '"'::text) AS value
FROM (
SELECT xyz.eid
, (xyz.value -> 'type'::text) AS type
, (xyz.value -> 'value'::text) AS value
FROM (
WITH reports(data) AS (
SELECT (e_1.data)::json AS data
, e_1.eid
FROM entities_history e_1
WHERE e_1.version = (SELECT max(i.version) AS max FROM entities_history i WHERE i.eid = e_1.eid)
-- AND eid = 99485
-- AND bulk_load_id = '506100'
)
SELECT r.data, r.eid, obj.value
FROM reports r
, LATERAL json_array_elements((r.data #> '{alternateIds}'::text[])) obj(value)) xyz
) u
WHERE (btrim((u.type)::text, '"'::text) = 'GOLD_BRANCH_ID'::text)
)
SELECT
e.type AS entitytype,
e.id AS entityid,
e.root_id AS party_id,
h.parent_eid as parent_eid,
aid.value AS scidvalue,
gsl6.value AS gsl6,
gold_id.value AS gold_id,
branch_id.value AS branch_id,
btrim(((h.data -> 'legalName'::text))::text, '"'::text) AS legalname
FROM entities_history h
INNER JOIN (
SELECT eid, source_id, ROW_NUMBER() OVER(PARTITION BY eid ORDER BY CASE source_type WHEN 'GETS-SSS' THEN 1 WHEN 'REQUEST' THEN 2 ELSE '9' END ASC, version DESC) AS rn
FROM entities_history
) ext_source_ident
ON ext_source_ident.eid = h.eid
