How to insert large JSON file to database with PHP

Question

So, I have a large JSON file and I want to insert data from that file to MySQL database. I can only use PHP 5.6 and can't change php.ini file.

When I'm using json_decode(), I get error, that there is to much memory to allocate. So I searched for some kind of library and I found this library and I'm using it like that:

set_time_limit(300);
$listener = new \JsonStreamingParser\Listener\InMemoryListener();
$stream = fopen('data/stops.json', 'r');
try {
    $parser = new \JsonStreamingParser\Parser($stream, $listener);
    $parser->parse();
    fclose($stream);
} catch (Exception $e) {
    fclose($stream);
    throw $e;
}
var_dump($listener->getJson());

But I still get that annoying error about momory:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 72 bytes) in SOME/PATH/TO/vendor/salsify/json-streaming-parser/src/Parser.php on line 516

I have no clue how to obtain my JSON file. So I'm looking for some advice, or someone who can help me to write a code that will be responsible for converting JSON file to array, so I colud insert data from that array to database. Also I'm not looking for one time solution, becouse I need to parse that JSON a least one per day.

Here is the whole JSON file: JSON, the structure looks like this:

{
    "2017-07-26":
        {
            "lastUpdate":"2017-07-26 07:07:01",
            "stops":[
                {
                    "stopId":32640,
                    "stopCode":null,
                    "stopName":null,
                    "stopShortName":"2640",
                    "stopDesc":"Amona",
                    "subName":"2640",
                    "date":"2017-07-26",
                    "stopLat":54.49961,
                    "stopLon":18.44532,
                    "zoneId":null,
                    "zoneName":null,
                    "stopUrl":"",
                    "locationType":null,
                    "parentStation":null,
                    "stopTimezone":"",
                    "wheelchairBoarding":null,
                    "virtual":null,
                    "nonpassenger":null,
                    "depot":null,
                    "ticketZoneBorder":null,
                    "onDemand":null,
                    "activationDate":"2017-07-25"
                },
                {...},
                {...}
            ]
        }
}

you can try with a `.htaccess` file with this in it `php_value memory_limit 200M` (example for 200M). I think it depends on your config, but worked for me — Kaddath, Jul 26 '17 at 14:47
yeah, but what when I will need more and more memory to parse my array, I'm looking for some long shot solution, becosue im' not sure how large my JSON file will be in the future and I don't want to set that limit to unlimited o something liek that — Panczur, Jul 26 '17 at 14:51
you could also read the file line-by-line but that will force you to stop and start via regular expressions - which will result in spaghetti code, you will pretty much be forced to re-invent the JSON wheel — specializt, Jul 26 '17 at 14:53
You might wish to use something like this: https://github.com/kuma-giyomu/JSONParser/tree/master/package but ideally i'd say preprocess the data on your work pc where you have full control into batches and dispatch those to the server. — Tschallacka, Jul 26 '17 at 14:53
if your JSON file is a repeated structure of similar elements, you can modify your script to be able to process multiple files, and split your JSON.. — Kaddath, Jul 26 '17 at 14:58
Yeah, I also thought about reading file line-by-line, maybe I will do that, but for now I'm looking for some kind more universal and faster solution ;) — Panczur, Jul 26 '17 at 14:59
@Kaddath, that's actually a good idea, I will go to search something — Panczur, Jul 26 '17 at 15:01

specializt · Accepted Answer · 2017-07-27T07:45:56.750

1

You need to set the option via ini_set : http://php.net/manual/en/function.ini-set.php

ini_set('memory_limit','16M');

taken from https://davidwalsh.name/increase-php-memory-limit-ini_set

alternatively, you can use a .htaccess file :

php_value memory_limit '512M'

credit goes to https://stackoverflow.com/a/42578190/351861

edited Jul 27 '17 at 07:45

answered Jul 26 '17 at 14:51

specializt

1,913
15
26

To be honses, I'm a little bit supprised, I have some cheap hosting server and they blocked me to edit php.ini file but that trick with .htaccess works; but I'm still curious how can I somehwo chunk my json file and maybe in parts convert it to `json_decode()` – Panczur Jul 26 '17 at 14:58

How to insert large JSON file to database with PHP

1 Answers1