CODEWITHSUNDEEP
azure-data-factory
Sagar
Sagar

Reputation: 710

How to remove random header from Azure Data factory lookup activity

I am querying an Azure SQL table in ADF Lookup activity My query looks like this

@concat('select distinct ltrim(rtrim(copay)) as column1 from ',item().TABLE_NAME,' where column1 is not null for json auto')

The output in Preview option looks like below

Preview of Lookup

I want to remove highlited random JSON header from output

I am passing this value to Web API CALL and I need value inside Square brackets

activity('GetLookupValues').output.value[0]

Upvotes: 0

Views: 400

Answers (1)

Graeme
Graeme

Reputation: 1204

One way is to wrap the JSON query in a CAST statement.

@concat( 'SELECT CAST( (SELECT DISTINCT t.CoPay AS column1 FROM ', item().TABLE_NAME, ' t WHERE t.CoPay IS NOT NULL FOR JSON AUTO) AS NVARCHAR(MAX))')

Upvotes: 0

Related Questions