How to server-side process a big JSON file?

jsosa

Hello, I currently have data sets of 2-3k rows, but may need to support around 25k rows. A full JSON file gets periodically generated with a background scheduled process, which is formatted appropriately for datatables.

How would I go about consuming that JSON file but with server-side processing in PHP? The examples I see on this site show using a database, but there's no database for this table. It's just the single JSON file.

kthorngren

'm not familiar with PHP but in general you using a flat file for this probably won't work well. Server side processing relies on protocol documented here and expects only the rows displayed on the page to be returned. Accessing the desired rows from a flat file would be cumbersome and probably not very performant. My recommendation would be to dump the data into a database for use with server side processing.

Kevin

jsosa

I was able to figure out a way to do this with array_slice, along with ordering, custom filters, and searching on a couple specific columns that I allow (called id and key).

I think I'm ok for now, and I'll post what I did for any suggestions or for anyone else to reference.

Example Javascript:

          var table = $('#table', context).DataTable({
            "serverSide": true,
            "ajax": {
              "url": "/my_ajax_path",
              "data": function ( d ) {
                return $.extend({}, d, {
                  // Get filter form values
                  "myfield_radio": document.querySelector('input[name="myfield_radio"]:checked').value,
                  "myfield_min": parseFloat(document.getElementById('myfield_min').value),
                  "myfield_max": parseFloat(document.getElementById('myfield_max').value),
                });
              }
            }
          });

Example PHP:

    // Get JSON file
    $path = 'myjsonfile.json';
    $contents = file_get_contents($path);
    $data = json_decode($contents, true);

    // Initialize table elements
    $columns = $request->get('columns');
    $dir = $request->get('order')[0]['dir'];
    $col = $columns[$request->get('order')[0]['column']]['name'];

    // Search
    $search = urldecode($request->get('search')['value']);
    $rows = [];
    foreach ($data['data'] as $row) {
      if (
        (empty($search) || (!empty($search) && (strstr($row['key'], $search) || strstr($row['id'], $search)))) &&
        ($request->get('myfield_radio') === "1" && $row['show'] === 1 ||
          $request->get('myfield_radio') === "2" && $row['show'] === 0 ||
          $request->get('myfield_radio') === "3")
      ) {
        $rows[] = $row;
      }
    }
    
    // Sort
    uasort($rows, fn($a, $b) => ($dir === 'asc') ? $a[$col] <=> $b[$col] : $b[$col] <=> $a[$col]);
    
    // Page
    $data_slice = array_slice($rows, $request->get('start'), $request->get('length'));

    return new JsonResponse(
      array(
        'draw' => intval($request->get('draw')),
        'recordsTotal' => count($data['data']),
        'recordsFiltered' => count($rows),
        'data' => $data_slice
      )
    );

colin

Nice, thanks for posting.

Colin

jsosa

A few follow-up notes since I can't edit my post.

With more custom filters, I needed to add "type": "POST" to the ajax call since I got a URI too long error without it.

The PHP function used for a simple string search, strstr, needs to be stristr to be case insensitive. I also ended up replacing it with something like stripos($row['key'], $search) !== false since the PHP docs say it's faster and less memory intensive. Regex could also be used. And a more generalized version of it should check each column's searchable value, probably as a helper function replacing the str functions it in my example.

On client side, I wanted to note that I have custom filters as form fields where submit is tied to a listener that does a table.draw(); after some validation.

Thanks!