PostgreSQL: Create Index in JSON Array
<p>I am pretty new to postregSQL and not too familiar with SQL yet. But im trying to learn.
In my database i want to store huge JSON files (~2mio lines, 40mb) and later query them as fast as possible. Right now it is to slow, so i figured indexing should do the trick.
The Problem is i do not know how to index the file since it is a bit tricky. I am woking on it the whole day now and starting to get desperate..</p>

<p>My DB is calles "replays" the json column "replay_files"</p>

<p>So my files look like this:</p>

<pre><code>"replay": [
    {
        "data": {
            "posX": 182,
            "posY": 176,
            "hero_name": "CDOTA_Unit_Hero_EarthSpirit"
        },
        "tick": 2252,
        "type": "entity"
    },
    {
        "data": {
            "posX": 123,
            "posY": 186,
            "hero_name": "CDOTA_Unit_Hero_Puck"
        },
        "tick": 2252,
        "type": "entity"
    }, ...alot more lines... ]}
</code></pre>

<p>I tried to get all the entries with say heron_name: Puck
So i tried this: </p>

<p>SELECT * FROM replays r, json_array_elements(r.replay_file#>'{replay}') obj WHERE obj->'data'->>'hero_name' = 'CDOTA_Unit_Hero_Puck';</p>

<p>Which is working but for smaller files.
So i want to index like that:</p>

<p>CREATE INDEX hero_name_index ON
    replays ((json_array_elements(r.replay_file#>'{replay}')->'data'->'hero_name);</p>

<p>BUt it doesn work. I have no idea how to reach that deep into the file and get to index this stuff.</p>

<p>I hope you understand my problem since my english isnt the best and can help me out here. I just dont know what else to try out.</p>

<p>Kind regards and thanks alot in advance
Peter</p>