title: SQL Query to JSON
body: <p>Hi I am new to JSON and would like to ask this question:</p>
  
  <p>So my goal is trying to make the result look like this:</p>
  
  <pre><code>{
      "userid": "10293304" ,     "segments": ["Online_Flag"]
  } 
  
  {
      "userid": "10292298" , "segments": ["schedule_Appointment", "Enrolled_Order","Complete_Order"]
  
  }
  </code></pre>
  
  <p>I was able to use T SQL to query the result to look like this:</p>
  
  <pre><code>[{"userid":"10293159","segments":[{"segment":"Schedule_Appointment"}]},
  {"userid":"10293056","segments":[{"segment":"Schedule_Appointment"}]},
  {"userid":"10292838","segments":[{"segment":"Schedule_Appointment"}]},
  </code></pre>
  
  <p>The issue is that I only need to show the JSON value (Schedule_Appointment), not the key(segment)</p>
  
  <p>is there any way (using TSQL) to hide the keys and only show the values in JSON Array?</p>
  
  <p>I have tried to look for ways to create JSON array using SQL but no luck, if anyone can explain I would really appreciate.</p>
  
  <p>SQL SERVER 2016 </p>
  
  <p>Data</p>
  
  <pre><code>CREATE TABLE [dbo].[Lotame_JSON](
  [lead_id] [varchar](100) NULL,
  [dist_date_key] [date] NULL,
  [online_flag] [int] NULL,
  [sched_appt_qty] [int] NULL,
  [enroll_order_qty] [int] NULL,
  [compl_order_qty] [int] NULL)
  
  insert into [dbo].[Lotame_JSON]
  values('105646','2016-12-1',1,0,0,1)
  
  insert into [dbo].[Lotame_JSON]
  values('125646','2016-12-1',0,0,1,0)
  
  insert into [dbo].[Lotame_JSON]
  values('112646','2016-12-1',0,1,1,0)
  
  insert into [dbo].[Lotame_JSON]
  values('106446','2016-12-1',0,0,1,0)
  </code></pre>
  
  <p>Query I have:</p>
  
  <pre><code>select 
  [lead_id] AS 'userid',
  (
  SELECT segment
      from 
      (
          SELECT              
              CASE WHEN [online_flag] &gt; 0 THEN '1' else null end as 'online_flag',
              CASE WHEN sched_appt_qty &gt; 0 THEN '2' else null end as 'Schedule_Appointment',
              CASE WHEN enroll_order_qty &gt; 0 THEN '3'  else null end as 'Enrolled_Order',
              CASE WHEN compl_order_qty &gt; 0 THEN '4'  else null end as 'Complete_Order'
          FROM [dbo].[Lotame_JSON] as sub
          WHERE sub.lead_id = main.lead_id
      ) t
      UNPIVOT
      (
          segment1
          for segment in (online_flag,Schedule_Appointment,Enrolled_Order,Complete_Order)
      ) as UnPvot
      for JSON PATH
  ) AS 'segments' 
  from [dbo].[Lotame_JSON] as main
  where online_flag = 0 and (sched_appt_qty &gt; 0 or enroll_order_qty &gt; 0   or    compl_order_qty &gt; 0)
  and dist_date_key &gt;= '2016-9-1'
  FOR JSON PATH
  </code></pre>
  
  <p>Thanks</p>