How to execute json query in talend job for postgres database

Anonymous — Tue, 16 Jan 2018 10:27:07 GMT

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

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

lojdr — Tue, 16 Jan 2018 12:10:03 GMT

Hello,

what errors do you see?

Regards

Lojdr

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

Anonymous — Wed, 17 Jan 2018 09:02:21 GMT

Attaching screenshot of error.

json_query_error.PNG

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

lojdr — Wed, 17 Jan 2018 09:33:25 GMT

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

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

Anonymous — Wed, 17 Jan 2018 10:57:04 GMT

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

json_query_error.PNG

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

lojdr — Wed, 17 Jan 2018 11:20:16 GMT

Hello,

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

Regards

Lojdr

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

How to execute json query in talend job for postgres database

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

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

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

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

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