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?

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 | |
  • 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 Jul 28 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 Jul 28 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 Jul 28 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 Jul 28 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 | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.