How do I get values from inside nested json array without names?-CodePudding

This is the following JSON I am working with. I have everything being pulled and added correctly all except the content under "tactics": which comes out as Array.

My goal is to store the values as a comma-delimited.

'[
{
  "queryFrequency": "P1D",
  "queryPeriod": "P1D",
  "triggerOperator": "GreaterThan",
  "triggerThreshold": 0,
  "eventGroupingSettings": {
    "aggregationKind": "SingleAlert"
  },
  "severity": "Medium",
  "query": "let extess",
  "suppressionDuration": "PT1H",
  "suppressionEnabled": false,
  "tactics": [
    "Execution",
    "Persistence"
  ],
  "displayName": "MFA disabled for a user",
  "enabled": true,
  "description": "Multi-Factor Authentication (MFA) helps prevent credential compromise. This alert identifies when an attempt has been made to diable MFA for a user ",
  "alertRuleTemplateName": "65c78944-930b-4cae-bd79-c3664ae30ba7",
  "lastModifiedUtc": "2021-06-16T16:29:52.6974983Z",
  "name": "1ada95bc-b4d5-4776-bc3e-2dbb3684c0b1",
  "id": "/sc0b1",
  "kind": "Scheduled",
  "createIncident": true,
  "groupingConfiguration": {
    "enabled": false,
    "reopenClosedIncident": false,
    "lookbackDuration": "PT5H",
    "entitiesMatchingMethod": "All",
    "groupByEntities": [
      "Account",
      "Ip",
      "Host",
      "Url",
      "FileHash"
    ]
  },
  "playbookName": ""
},
{
  "queryFrequency": "P1D",
  "queryPeriod": "P1D",
  "triggerOperator": "GreaterThan",
  "triggerThreshold": 0,
  "eventGroupingSettings": {
    "aggregationKind": "SingleAlert"
  },
  "severity": "Medium",
  "query": "StppUsed",
  "suppressionDuration": "PT1H",
  "suppressionEnabled": false,
  "tactics": [
    "Execution",
    "Persistence"
  ],
  "displayName": "Explicit MFA Deny",
  "enabled": true,
  "description": "User explicitly denies MFA push, indicating that login was not expected and the account\'s password may be compromised.",
  "alertRuleTemplateName": "a22740ec-fc1e-4c91-8de6-c29c6450ad00",
  "lastModifiedUtc": "2021-06-16T16:29:54.0826821Z",
  "name": "bba57ceb-dd33-4297-8080-b19b1bd07a21",
  "id": "/suobba5d07a21",
  "kind": "Scheduled",
  "createIncident": true,
  "groupingConfiguration": {
    "enabled": false,
    "reopenClosedIncident": false,
    "lookbackDuration": "PT5H",
    "entitiesMatchingMethod": "All",
    "groupByEntities": [
      "Account",
      "Ip",
      "Host",
      "Url",
      "FileHash"
    ]
  },
  "playbookName": ""
    }  ]'

This is the code:

...
$dep_cols=array("queryFrequency","queryPeriod","triggerOperator","triggerThreshold","aggregationKind","severity","query","suppressionDuration","suppressionEnabled","tactics","displayName","enabled","description","kind","createIncident","playbookName");  // declare columns
    $dep_keys=array_map(function($v){return ":$v";},$dep_cols);  // build :keys    
    $dep_cols=array_combine($dep_keys,$dep_cols);   // assign :keys
    var_export($dep_cols);
    $dep_query="INSERT INTO `template_rules` (`id`,`".implode('`,`',$dep_cols)."`)"; // list columns as csv
    $dep_query.=" VALUES ('',".implode(',',array_keys($dep_cols)).");";
    echo "<div>$dep_query</div>";
    $stmt_add_dep=$db->prepare($dep_query);
        
    foreach(json_decode($json) as $d){
        foreach($dep_cols as $k=>$v){
            if($k==':tactics'){$v=json_decode($v);}
            $stmt_add_dep->bindValue($k,(property_exists($d,$v)?$d->$v:""));
            echo "<div>$k => {$d->$v}</div>";
        }
        $stmt_add_dep->execute();
        echo "<div>Dep Affected Rows: ",$stmt_add_dep->rowCount(),"</div><br>";
    }
...

If I remove the f($k==':tactics') statement I just get an Array. I'm not sure how to pull those values out as they look to just be a string in an array.

current results look like this:

...
:suppressionDuration => PT1H
:suppressionEnabled =>
:tactics =>
:displayName => MFA disabled for a user
:enabled => 1
...

CodePudding user response：

Here's a working refactor of your script.

Create arrays of the whitelisted column names and a number of placeholders (I prefer the vague ?, but you can use named placeholders if you like).

Create separate payloads of values to be fed to the prepared statement when execute() is called.

You don't need to mention id if you are autoincrementing that column.

Code: (PHPize.online Demo)

$whitelist = [
    "queryFrequency", "queryPeriod", "triggerOperator", "triggerThreshold",
    "aggregationKind", "severity", "query", "suppressionDuration",
    "suppressionEnabled", "tactics", "displayName", "enabled",
    "description", "kind", "createIncident", "playbookName"
];

$columns = [];
$placeholders = [];
$valueSets = [];
foreach ($whitelist as $column) {
    $columns[] = "`$column`";
    $placeholders[] = "?";
}
foreach (json_decode($json) as $i => $obj) {
    $obj->aggregationKind = $obj->eventGroupingSettings->aggregationKind ?? null;
    $obj->tactics = property_exists($obj, 'tactics') ? implode(',', $obj->tactics) : null;
    foreach ($whitelist as $column) {
        $valueSets[$i][] = $obj->$column ?? null;
    }
}
    
$stmt = $pdo->prepare(
    sprintf(
        'INSERT INTO `template_rules` (%s) VALUES (%s)',
        implode(',', $columns),
        implode(',', $placeholders)
    )
);
foreach ($valueSets as $values) {
    $stmt->execute($values);
    printf("<div>New autoincremented Id: %d</div><br>\n\n", $pdo->lastInsertId());
}

echo json_encode($pdo->query('SELECT * FROM template_rules')->fetchAll(PDO::FETCH_ASSOC), JSON_PRETTY_PRINT);