A Simple JSON Example

Question: How do I dump one row to a json file?

Answer: Here's an adhoc query that writes all the Foxhound 5 Monitor Options values for one target database to a json text file:

-- Start - Foxhound5 - 2 Adhoc Query via ISQL
UNLOAD
SELECT REPLACE ( json_raw_table.json_raw_string, ',"', ',\X0D\X0A"' ) AS monitor_options_json
  FROM ( SELECT *
           FROM alerts_criteria
          WHERE alerts_criteria.criteria_set_type = 'Target Specific'
            AND alerts_criteria.sampling_id       = 13 -- Inventory17
            FOR JSON RAW
       ) AS json_raw_table ( json_raw_string ) 
TO 'C:\\temp\\monitor_options.json'
   DELIMITED BY '' 
   ESCAPES OFF 
   HEXADECIMAL OFF 
   QUOTES OFF;

[{"criteria_set_type":"Target Specific",
"sampling_id":13,
"send_alert_emails":"N",
"mail_recipient":"",
"use_html_in_emails":"Y",
"attach_files_to_emails":"Y",
"email_url_host_port":"localhost",
"display_alerts":"Y",
"autodrop_mail_recipients":"",
"autodrop_mail_recipients_same_as_for_alerts":"Y",
"previous_sample_set_number_processed":3935794,
"previous_gathered_sample_finished_at":"2020-12-10 08:28:08.609",
"current_sample_finished_at":"2020-12-10 08:28:08.609",
"alert1_enabled":"Y",
"alert1_is_in_effect":"N",
"alert1_in_effect_at":"2020-12-07 11:13:00.567",
"alert1_all_clear_at":"2020-12-08 07:48:11.438",
"alert1_threshold_lost_sample_accumulated_msec":60000,
"alert1_actual_lost_sample_accumulated_msec":0,
...

The docs are light on simple questions like this, perhaps because they lead to more simple questions...

How do I get SELECT FOR JSON output into a string?

Answer: Use a SELECT INTO clause, or a subquery.

How do I actually code SELECT FOR JSON in a subquery?

Answer: Code a derived table with an alias table name and a single alias column name, because the select list for SELECT * FOR JSON consists of one single column.

How do I get line breaks?

Answer: Use REPLACE, and hope your data doesn't contain any comma-doublequote pairs... or write better code than this example :)