How to execute json query in talend job for postgres database

Six Stars PK
Six Stars

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

 

Tags (1)
Eight Stars

Re: How to execute json query in talend job for postgres database

Hello,

 

what errors do you see? 

 

Regards

Lojdr

Six Stars PK
Six Stars

Re: How to execute json query in talend job for postgres database

Attaching screenshot of error.

Eight Stars

Re: How to execute json query in talend job for postgres database

Hello,

 

can you try to use escape sequences in your query for double quotas?

E.g.:

 btrim((u.type)::text, '\"'::text) AS type

Instead of 

 btrim((u.type)::text, '"'::text) AS type

 

Regards

Lojdr

Six Stars PK
Six Stars

Re: How to execute json query in talend job for postgres database

Hi,

 

Thanks for your reply, yes i have fixed it , but now i am getting different error.(attached error screenshot)

Do you think with statement in query is creating the problem.

 

Thanks

PK

Eight Stars

Re: How to execute json query in talend job for postgres database

Hello,

 

Do you have the query in double quotas in the job definition (at the beginning and at the end)?Capture1.JPG

 

 

 

 

 

 

Regards

Lojdr