Remove extra brackets in a JSON Array for compatibility in DataTable-CodePudding

I return JSON to my RestAPI connection and Deserialize it to DataTable. This work if I am using only one Array, but I have a Array in Array because there are two different types of Database tables and I need to see both of them in one DataTable.

My DataTable can't work with the extra brackets there you can see around my first item in my JSON below.

I need a method to remove them or another Solution to come my goal a little bit near.

Here I want to get data from two different tables, I don't want to mix them like inner join does, that's the reason why I tried to solve this with a foreach and store it in a Array.

Actually it does what I want but the extra brackets make troubles.

How can I remove them or is there a other Solution?

PHP

$arr = array(
    "Massnahmenliste",
    "Terminliste"
);
$data = array();
foreach($arr as $table)
{ 
    $data[] = \DB::table($table)
    ->join('Firma', $table.'.id_Firma', '=', 'Firma.id_Firma') 
    ->select($table.'.*', 'Firma.*')
    ->orderBy('Termin')
    ->get();
}
return $data;

var options = new RestClientOptions(GetUrl)
{
    ThrowOnAnyError = true
};
var client = new RestClient(options);
RestRequest request = new RestRequest();
RestResponse response = client.Get(request);

using (DataTable dt = (DataTable)JsonConvert.DeserializeObject(response.Content, (typeof(DataTable))))();

JSON:

[
   [
      [
         {
            "id_Massnahmenliste":2,
            "id_Firma":1,
            "Termin":"2022-01-01 10:10:10",
            "Beschreibung":"Beschreibungs Test",
            "MassnahmenNr":100,
            "Abgeschlossen":0,
            "id_Terminliste":0,
            "ServiceNr":0,
            "Servicestand":"0",
            "Firma":"Coding GmbH",
            "Strasse":"Spitalstr",
            "Hausnummer":"4",
            "Postleitzahl":77652,
            "Ort":"Offenburg",
            "Vertragsbeginn":"2022-08-12",
            "Vertragsende":"2025-08-31"
         }
      ],
      [
         {
            "id_Terminliste":1,
            "id_Firma":1,
            "Termin":"2022-08-31 00:00:00",
            "Beschreibung":"Servicetermin",
            "ServiceNr":1,
            "Servicestand":"1 von 4",
            "Abgeschlossen":1,
            "id_Massnahmenliste":0,
            "MassnahmenNr":0,
            "Firma":"Coding GmbH",
            "Strasse":"Spitalstr",
            "Hausnummer":"4",
            "Postleitzahl":77652,
            "Ort":"Offenburg",
            "Vertragsbeginn":"2022-08-12",
            "Vertragsende":"2025-08-31"
         },
         {
            "id_Terminliste":5,
            "id_Firma":2,
            "Termin":"2022-08-31 00:00:00",
            "Beschreibung":"Servicetermin",
            "ServiceNr":1,
            "Servicestand":"1 von 4",
            "Abgeschlossen":0,
            "id_Massnahmenliste":0,
            "MassnahmenNr":0,
            "Firma":"Coding GmbH",
            "Strasse":"Spitalstr",
            "Hausnummer":"4",
            "Postleitzahl":77652,
            "Ort":"Offenburg",
            "Vertragsbeginn":"2022-08-12",
            "Vertragsende":"2025-08-31"
         },
         {
            "id_Terminliste":9,
            "id_Firma":3,
            "Termin":"2022-08-31 00:00:00",
            "Beschreibung":"Servicetermin",
            "ServiceNr":1,
            "Servicestand":"1 von 4",
            "Abgeschlossen":0,
            "id_Massnahmenliste":0,
            "MassnahmenNr":0,
            "Firma":"Coding GmbH",
            "Strasse":"Spitalstr",
            "Hausnummer":"4",
            "Postleitzahl":77652,
            "Ort":"Offenburg",
            "Vertragsbeginn":"2022-08-12",
            "Vertragsende":"2025-08-31"
         },
         {
            "id_Terminliste":2,
            "id_Firma":1,
            "Termin":"2022-11-30 00:00:00",
            "Beschreibung":"Servicetermin",
            "ServiceNr":2,
            "Servicestand":"2 von 4",
            "Abgeschlossen":0,
            "id_Massnahmenliste":0,
            "MassnahmenNr":0,
            "Firma":"Coding GmbH",
            "Strasse":"Spitalstr",
            "Hausnummer":"4",
            "Postleitzahl":77652,
            "Ort":"Offenburg",
            "Vertragsbeginn":"2022-08-12",
            "Vertragsende":"2025-08-31"
         },
         {
            "id_Terminliste":6,
            "id_Firma":2,
            "Termin":"2022-11-30 00:00:00",
            "Beschreibung":"Servicetermin",
            "ServiceNr":2,
            "Servicestand":"2 von 4",
            "Abgeschlossen":0,
            "id_Massnahmenliste":0,
            "MassnahmenNr":0,
            "Firma":"Coding GmbH",
            "Strasse":"Spitalstr",
            "Hausnummer":"4",
            "Postleitzahl":77652,
            "Ort":"Offenburg",
            "Vertragsbeginn":"2022-08-12",
            "Vertragsende":"2025-08-31"
         },
         {
            "id_Terminliste":10,
            "id_Firma":3,
            "Termin":"2022-11-30 00:00:00",
            "Beschreibung":"Servicetermin",
            "ServiceNr":2,
            "Servicestand":"2 von 4",
            "Abgeschlossen":0,
            "id_Massnahmenliste":0,
            "MassnahmenNr":0,
            "Firma":"Coding GmbH",
            "Strasse":"Spitalstr",
            "Hausnummer":"4",
            "Postleitzahl":77652,
            "Ort":"Offenburg",
            "Vertragsbeginn":"2022-08-12",
            "Vertragsende":"2025-08-31"
         },
         {
            "id_Terminliste":3,
            "id_Firma":1,
            "Termin":"2023-02-28 00:00:00",
            "Beschreibung":"Servicetermin",
            "ServiceNr":3,
            "Servicestand":"3 von 4",
            "Abgeschlossen":0,
            "id_Massnahmenliste":0,
            "MassnahmenNr":0,
            "Firma":"Coding GmbH",
            "Strasse":"Spitalstr",
            "Hausnummer":"4",
            "Postleitzahl":77652,
            "Ort":"Offenburg",
            "Vertragsbeginn":"2022-08-12",
            "Vertragsende":"2025-08-31"
         },
         {
            "id_Terminliste":7,
            "id_Firma":2,
            "Termin":"2023-02-28 00:00:00",
            "Beschreibung":"Servicetermin",
            "ServiceNr":3,
            "Servicestand":"3 von 4",
            "Abgeschlossen":0,
            "id_Massnahmenliste":0,
            "MassnahmenNr":0,
            "Firma":"Coding GmbH",
            "Strasse":"Spitalstr",
            "Hausnummer":"4",
            "Postleitzahl":77652,
            "Ort":"Offenburg",
            "Vertragsbeginn":"2022-08-12",
            "Vertragsende":"2025-08-31"
         }
      ]
   ]
]

CodePudding user response：

Try to merge Arrays

$arr = array(
    "Massnahmenliste",
    "Terminliste"
);
$data = array();
foreach($arr as $table)
{ 
    $temp = \DB::table($table)
    ->join('Firma', $table.'.id_Firma', '=', 'Firma.id_Firma') 
    ->select($table.'.*', 'Firma.*')
    ->orderBy('Termin')
    ->get();
    $data = array_merge($temp,$data)
}
return $data;

CodePudding user response：

I found my own solution, I'm sure it's not very clean, but it does what I need and it works.

Remember that these are two different tables and are not related to each other.

First, I can't use array_push since I'm using Lavarel. So this is how I solved my problem with additional brackets around the first of two tables in a Array...

First I get all my information and put it in an array, then I replace some characters in a foreach and append it to a string. After that, I subtract characters from the whole string that were previously automatically placed by the conversion.

The last part just gives me the option to check if any of the tables is null then it replies "[ ,'" which I also replace with just "[".

$data = array();
            
           
$data[] = \DB::table('Massnahmenliste')
->join('Firma', 'Massnahmenliste.id_Firma', '=', 'Firma.id_Firma') 
->select('Massnahmenliste.*', 'Firma.*')
->orderBy('Termin')
->get();
$data[] = \DB::table('Terminliste')
->join('Firma', 'Terminliste.id_Firma', '=', 'Firma.id_Firma') 
->select('Terminliste.*', 'Firma.*')
->orderBy('Termin')
->get();


$string = "";


foreach(array_chunk($data, 2) as $value) {
    
    $string .= "{{$value[0]} {$value[1]}}";
    $string = str_replace("]", "",   $string);
    $string = str_replace("[", ",",   $string);
  
}

$string = str_replace("}{", "},{",   $string);
$string = substr($string, 2, strlen($string) - 3);
$string = $string[1]."[".substr($string,0);
$string = substr($string, 1, strlen($string) - 2);
$string .="}]";

if (strpos($string, '[ ,') === 0) 
{
    $string = str_replace("[ ,", "[",   $string);
}

return  $string;

BEFORE

[
[
    {
        "id_Massnahmenliste":3,
        "id_Firma":4,
        "Termin":"2022-09-11 00:00:00",
        "Beschreibung":"Nachkontrolle",
        "MassnahmenNr":0,
        "Abgeschlossen":0,
        "id_Terminliste":0,
        "ServiceNr":0,
        "Servicestand":null,
        "Firma":"Developer Akademie",
        "Strasse":"Street",
        "Hausnummer":"4",
        "Postleitzahl":1234,
        "Ort":"Ort",
        "Vertragsbeginn":"2022-08-14",
        "Vertragsende":"2025-08-31"
    }
],
[
    {
        "id_Terminliste":13,
        "id_Firma":4,
        "Termin":"2022-08-31 00:00:00",
        "Beschreibung":"Servicetermin",
        "ServiceNr":1,
        "Servicestand":"1 von 12",
        "Abgeschlossen":1,
        "id_Massnahmenliste":0,
        "MassnahmenNr":0,
        "Firma":"Developer Akademie",
        "Strasse":"Street",
        "Hausnummer":"4",
        "Postleitzahl":1234,
        "Ort":"Ort",
        "Vertragsbeginn":"2022-08-14",
        "Vertragsende":"2025-08-31"
    },
    {
        "id_Terminliste":14,
        "id_Firma":4,
        "Termin":"2022-09-30 00:00:00",
        "Beschreibung":"Servicetermin",
        "ServiceNr":2,
        "Servicestand":"2 von 12",
        "Abgeschlossen":0,
        "id_Massnahmenliste":0,
        "MassnahmenNr":0,
        "Firma":"Developer Akademie",
        "Strasse":"Street",
        "Hausnummer":"4",
        "Postleitzahl":1234,
        "Ort":"Ort",
        "Vertragsbeginn":"2022-08-14",
        "Vertragsende":"2025-08-31"
    }
]

]

AFTER

[
    {
        "id_Massnahmenliste":3,
        "id_Firma":4,
        "Termin":"2022-09-11 00:00:00",
        "Beschreibung":"Nachkontrolle",
        "MassnahmenNr":0,
        "Abgeschlossen":0,
        "id_Terminliste":0,
        "ServiceNr":0,
        "Servicestand":null,
        "Firma":"Developer Akademie",
        "Strasse":"Street",
        "Hausnummer":"4",
        "Postleitzahl":12345,
        "Ort":"Ort",
        "Vertragsbeginn":"2022-08-14",
        "Vertragsende":"2025-08-31"
    },
    {
        "id_Terminliste":13,
        "id_Firma":4,
        "Termin":"2022-08-31 00:00:00",
        "Beschreibung":"Servicetermin",
        "ServiceNr":1,
        "Servicestand":"1 von 12",
        "Abgeschlossen":1,
        "id_Massnahmenliste":0,
        "MassnahmenNr":0,
        "Firma":"Developer Akademie",
        "Strasse":"Street",
        "Hausnummer":"4",
        "Postleitzahl":12345,
        "Ort":"Ort",
        "Vertragsbeginn":"2022-08-14",
        "Vertragsende":"2025-08-31"
    },
    {
        "id_Terminliste":14,
        "id_Firma":4,
        "Termin":"2022-09-30 00:00:00",
        "Beschreibung":"Servicetermin",
        "ServiceNr":2,
        "Servicestand":"2 von 12",
        "Abgeschlossen":0,
        "id_Massnahmenliste":0,
        "MassnahmenNr":0,
        "Firma":"Developer Akademie",
        "Strasse":"Street",
        "Hausnummer":"4",
        "Postleitzahl":12345,
        "Ort":"Ort",
        "Vertragsbeginn":"2022-08-14",
        "Vertragsende":"2025-08-31"
    }
]