PHP 7.2 – SQL Server 2017 – Create Nested Array response

I have a Stored Procedure that retrieves data from a SQL Server DB.

With query result I need to populate the parameters’ Array of a SOAPClient’s Method

At present I’m manually creating the Array, but I was wondering if it was possible (and worthy) to create the Array in the way required by the SOAP Method directly from TSQL:

Using PHP 7.2 – SQL Server 2017

Let me explain with an example: this is my Query Result:

Array
(
    [Key] => R******l
    [Password] => c*************z
    [AccountNumber] => 0****1
    [MeterNumber] => 2******5
    [ShipTimestamp] => 2020-10-29T10:24:19+01:00
    [ServiceType] => INTERNATIONAL_ECONOMY
    [PackagingType] => YOUR_PACKAGING
    [PreferredCurrency] => EUR
    [Weight_Units] => KG
    [TotalWeight] => 0.02
...
)

While it should return something like this:

Array
(
    [Authentication] => Array
    (
        [User] => Array
        (
            [Key] => R******l
            [Password] => c*************z
        )
    )

    [Client] => Array
    (
        [Account] => 0*******1
        [Meter] => 2*******5
    )

    [Shipment] => Array
    (
        [ShipTimestamp] => 2020-10-29T10:41:26+01:00
        [DropoffType] => REGULAR_PICKUP
        [ServiceType] => INTERNATIONAL_ECONOMY
        [PackagingType] => YOUR_PACKAGING
        [PreferredCurrency] => EUR
        [TotalWeight] => Array
        (
            [Units] => KG
            [Value] => 0.02
        )
    )
    ...
)

Is it possible and worthy?

Answer

You could return one row with one JSON column using FOR JSON PATH¹ in SQL and in PHP json_decode($json, true)³ with $assoc param set to true to decode JSON as array.

SELECT (
  SELECT user_key [Authentication.User.Key]
       , user_password  [Authentication.User.Password]
       , client_account [Client.Account]
       , client_meter   [Client.Meter]
      --- and so on
  FROM my_table
  FOR JSON PATH
) [json]

The result should be JSON

{ 
 "Authentication": { "User": { "Key": "XXX", "Password": "XXX" } },
 "Client": { "Account": "YYY", "Meter": 200500 }
}

And now You can fetch that value in PHP, decode it and supply to SOAP.

  $row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
  $json = json_decode($row['json'], true);

But in SQL You need a special syntax to format those types:

1. date values CONVERT(varchar(10), date_col, 120) – 120 results in 2020-10-29 11:32:00 format, varchar(10) trim just date part, varchar(20) gets whole date+time²
2. boolean values CAST(boolean_col as bit) (0 -> false, 1 -> true)

More info:

1. SQL: Format query results as JSON with FOR JSON
2. SQL: CAST and CONVERT with datetime in different formats
3. PHP: json_decode