Conver SQL result to json format

Question

I'm trying to convert my sql result into a json format for each line, although struggling with the syntax. The idea is to use ADF to pull some data, convert it to this format and feed the api.

The code that I tried is at the bottom. Any assists is greatly appreciated.

DROP TABLE IF EXISTS #tmp;

CREATE TABLE #tmp (
    id       int NOT NULL,
    [date]   date NOT NULL,
    docs     VARCHAR(15) NOT NULL,
    sales    numeric(7,2) NOT NULL,
    code1    int NOT NULL,
    code2    int NOT NULL,
    [desc]   VARCHAR(max) NOT NULL,
    num1     int NOT NULL
);

INSERT INTO #tmp VALUES
    ( '1', '2023-10-24', 'OB2', '0.00', '50','99','Test1','8' ),
    ( '2', '2023-10-24', 'OB2', '0.00', '50','99','Test2','8' ),
    ( '3', '2023-10-24', 'OB', '0.00', '50','99','Test3','8' ),
    ( '4', '2023-10-24', 'OB2', '0.00', '50','99','Test4','8' )

To json format:

{
  "requests": [
    {
      "method": "POST",
      "id": "1",
      "url": "xxx",
      "headers": {
        "Content-Type": "application/json"
      },
      "body": {
        "Date": "2020-10-24",
        "Docs": "OB2",
        "Sales": 0.00,
        "Code1": "50",
        "Code2": "99",
        "Desc": "Test1",
        "Num1": "8"
      }
    },
    {
      "method": "POST",
      "id": "2",
      "url": "xxx",
      "headers": {
        "Content-Type": "application/json"
      },
      "body": {
        "Date": "2020-10-24",
        "Docs": "OB2",
        "Sales": 0.00,
        "Code1": "50",
        "Code2": "99",
        "Desc": "Test2",
        "Num1": "8"
      }
    },
    {
      "method": "POST",
      "id": "3",
      "url": "xxx",
      "headers": {
        "Content-Type": "application/json"
      },
      "body": {
        "Date": "2020-10-24",
        "Docs": "OB",
        "Sales": 0.00,
        "Code1": "50",
        "Code2": "99",
        "Desc": "Test3",
        "Num1": "8"
      }
    },
    {
      "method": "POST",
      "id": "4",
      "url": "xxx",
      "headers": {
        "Content-Type": "application/json"
      },
      "body": {
        "Date": "2020-10-24",
        "Docs": "OB2",
        "Sales": 0.00,
        "Code1": "50",
        "Code2": "99",
        "Desc": "Test4",
        "Num1": "8"
      }
    }
  ]
}

I have tried this:

SELECT
    'POST' AS method,
    CONVERT(NVARCHAR(50), Id) AS id,
    'xxx' AS url,
    JSON_QUERY('{"Content-Type": "application/json"}') AS headers,
    JSON_QUERY('{
        "Date": "' + CONVERT(NVARCHAR(10), [date], 120) + '",
        "Docs": "' + docs + '",
        "Sales": ' + CONVERT(NVARCHAR(50), sales) + ',
        "Code1": "' + CONVERT(NVARCHAR(50), code1) + '",
        "Code2": "' + CONVERT(NVARCHAR(50), code2) + '",
        "Desc": "' + [desc] + '",
        "Num1": "' + CONVERT(NVARCHAR(50), num1) + '"
    }') AS body
FROM
    #tmp
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

Thanks in advance for the support.

edited tags

Answer 1

You need to use ROOT option to specify a named root element :

SELECT
    'POST' AS method,
    CONVERT(NVARCHAR(50), Id) AS id,
    'xxx' AS url,
    JSON_QUERY('{"Content-Type": "application/json"}') AS headers,
    JSON_QUERY('{
        "Date": "' + CONVERT(NVARCHAR(10), [date], 120) + '",
        "Docs": "' + docs + '",
        "Sales": ' + CONVERT(NVARCHAR(50), sales) + ',
        "Code1": "' + CONVERT(NVARCHAR(50), code1) + '",
        "Code2": "' + CONVERT(NVARCHAR(50), code2) + '",
        "Desc": "' + [desc] + '",
        "Num1": "' + CONVERT(NVARCHAR(50), num1) + '"
    }') AS body
FROM
    #tmp
FOR JSON PATH, ROOT('requests');

Demo here