0

I'm quite new to SQL and I've hit a road block in my knowledge. I am using Automation Studio to write a SQL query that takes the data from a data extension and transforms it into JSON. The idea will be to push the JSON data to another part of the business later on.

So I have written the following query:

SELECT 

id AS 'SubscriberKey',
First_Name AS 'FirstName',
Last_Name AS 'LastName',
Email AS 'EmailAddress',
Phone AS 'MobilePhone',
Cover_Type AS '[Need cover for]'

FROM [Health_Affiliate_Email_Capture]
FOR JSON PATH

Problem is I don't know how to tell the query to put the JSON into a field called Data that is in the same table.

Health_Affiliate_Email_Capture will be capturing data that comes in from a form.

Can someone please tell me what I am missing?

Improve this question

2 Answers 2

Reset to default
1

You just need to give the output of your query a column alias, "Data".

SELECT (
    SELECT 
        id AS [SubscriberKey],
        First_Name AS [FirstName],
        Last_Name AS [LastName],
        Email AS [EmailAddress],
        Phone AS [MobilePhone],
        Cover_Type AS [Need cover for]
    FROM
        [Health_Affiliate_Email_Capture]
    FOR JSON PATH 
) AS [Data]

Note: Query Studio uses its own layer of crude syntax checking in order to create the results Data Extension and will reject this query. Automation Studio's happy enough to run this type of query.

Improve this answer
4
  • Thanks! Just a quick follow-up to this. It won't let me save the query unless I reference the primary key field (id in this case), what's the best way to reference this?
    – Trevor Van As
    Commented Jul 28, 2020 at 4:17
  • FOR JSON PATH would create a JSON document containing an array of all the records in your Health_Affiliate_Email_Capture Data Extension. So the output contains one row, not a row for each id. If you're needing to output a JSON document, don't use a PK and just overwrite your results data extension and do a Data Extension Extract with no column header etc. There'd be only one column, "Data" in your results data extension and one row.
    – Macca
    Commented Jul 28, 2020 at 4:36
  • I tried to change it to Overwrite but I got the following error "Update Type must be 'Update' or the target data extension 'Health_Affiliate_Email_Capture' cannot appear in the query."
    – Trevor Van As
    Commented Jul 28, 2020 at 4:46
  • I'm a bit confused. FOR JSON PATH will create a single JSON document that you'd not be using to update every record. What are you wanting to "push to another part of the business later on"? How is the data going to be pushed? If you just want those field placed into JSON, you could just use string manipulation.
    – Macca
    Commented Jul 28, 2020 at 6:16
0

Not sure a query is the best way to do this. You could use the REST API to get DE rows as JSON .

Another option is LookupRows() in SSJS which will return an array of objects for each row. You could Stringify the result and http to external system if that’s your end goal.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .