Friday, April 5, 2019

How to create JSON output from NetSuite ODBC using SQL

JSON (JavaScript Object Notation) output can be generated using SQL via NetSuite ODBC interface.
To do this, the output needs to be in one field only. Therefore, fields need to be concatenated. For longer fields use combination of "concat" function and "+" operator.

Example:

select concat('','{"category_0":"' + category_0 + '", "companyname":"' + companyname + '", "full_name":"' + full_name + '", "email":"' + email + '"}') as data from customers where customer_id = 22

Output:

{"category_0":"CUSTOMER", "companyname":"Bay Media Research", "full_name":"Bay Media Research", "email":"maryc@baymedia.com"}

No comments:

Post a Comment