JSON conversion help

• olibbhq

  SSCarpal Tunnel

  Points: 4034

  More actions

  August 22, 2017 at 4:16 am

  #332300

  Hi

  I am struggling to get JSON output as required for bulk import into elasticsearch.

  The format required is:
  { "index": {}}
  { "product_name": "Apple iPhone 7"}
  { "index": {}}
  { "product_name": "Apple iPhone Lightning Cable" }
  { "index": {}}
  { "product_name": "Apple iPhone 6"}
  { "index": {}}
  { "product_name": "Samsung Galaxy S7" }
  { "index": {}}
  { "product_name": "Samsung Galaxy S6" }

  In SQL the table is:

  CREATE TABLE [dbo].[aa](
      [product_name] [nvarchar](50) NULL
  ) ON [PRIMARY]

  GO
  INSERT [dbo].[aa] ([product_name]) VALUES (N'Apple iPhone 7')
  GO
  INSERT [dbo].[aa] ([product_name]) VALUES (N'Apple iPhone Lightning Cable')
  GO
  INSERT [dbo].[aa] ([product_name]) VALUES (N'Apple iPhone 6')
  GO
  INSERT [dbo].[aa] ([product_name]) VALUES (N'Samsung Galaxy S7')
  GO

  So far I have SELECT
       '' as [index],
       product_name
  FROM aa
  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

  But dont really know where to go from here, the new line can just be a char(10) I assume but I dont know how to remove each comma from the output.

  Any help or guidance where to look would be very much appreciated.

  Thank you

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  August 22, 2017 at 11:28 am

  #1956214

  I would think you could take that last query and have it be the input to a REPLACE function where you just strip out any commas, perhaps.   Unless, of course, any of your values have commas in them.   So, kinda like this:

  SELECT REPLACE(
   (
   SELECT
      '' as [index],
      product_name
   FROM aa
   FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
   ), ',', '')

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)