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

 

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.

Highlighted
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

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog