Removing an array from a JSON file

[SimonMac]

I have a huge JSON file (10m rows and still counting as it's not fully opened yet) however 99.9% is useless to me. There are about 2200 records in the following format

{
"type" : "FeatureCollection",
"name" : "%filename%",
"features" : [
{
"type" : "Feature",
"geometry" : {=}
"properties" : {=} 
},
{
"type" : "Feature",
"geometry" : {=}
"properties" : {=}
},
.
.
.
]
}

The problem is the geometry array (if that is the right term) is a list of co-ordinates with anything from 7 - 21 thousand lines that are useless to me, this means the overall file is 5gb+

At the moment I am going through each record and manaully deleting the array, is there a way to automate it? I don't want to spend an day going through all 2200 records just to be given a new version at some point meaning I will have to do it again.

 

[eek]

I have a huge JSON file (10m rows and still counting as it's not fully opened yet) however 99.9% is useless to me. There are about 2200 records in the following format

{
"type" : "FeatureCollection",
"name" : "%filename%",
"features" : [
{
"type" : "Feature",
"geometry" : {=}
"properties" : {=}
},
{
"type" : "Feature",
"geometry" : {=}
"properties" : {=}
},
.
.
.
]
}

The problem is the geometry array (if that is the right term) is a list of co-ordinates with anything from 7 - 21 thousand lines that are useless to me, this means the overall file is 5gb+

At the moment I am going through each record and manaully deleting the array, is there a way to automate it? I don't want to spend an day going through all 2200 records just to be given a new version at some point meaning I will have to do it again.

delete NameOfJsonObject.type.features.geometry would work but that only works if it's in memory.

 

[fulcon]

Might be your edit but that's not valid JSON.

As you can't have line breaks in a property, you should be able to treat the 'geometry' line as a single line.

Because it's a large file, you don't want to read it all into memory in one go so this will do 5000 lines at a time:

[COLOR=#9cdcfe]$batchSize[/COLOR][COLOR=#d4d4d4] = [/COLOR][COLOR=#b5cea8]5000[/COLOR]

[COLOR=#dcdcaa]Get-Content[/COLOR][COLOR=#d4d4d4] E:\bigFile.json -ReadCount [/COLOR][COLOR=#9cdcfe]$batchSize[/COLOR][COLOR=#d4d4d4] |[/COLOR]
[COLOR=#d4d4d4]    [/COLOR][COLOR=#dcdcaa]Foreach-Object[/COLOR][COLOR=#d4d4d4] {[/COLOR]
[COLOR=#d4d4d4]        [/COLOR][COLOR=#9cdcfe]$_[/COLOR][COLOR=#d4d4d4] -replace [/COLOR][COLOR=#ce9178]"^\W*[/COLOR][COLOR=#d7ba7d]`"[/COLOR][COLOR=#ce9178]geometry[/COLOR][COLOR=#d7ba7d]`"[/COLOR][COLOR=#ce9178].+,$"[/COLOR][COLOR=#d4d4d4] |[/COLOR]
[COLOR=#d4d4d4]            [/COLOR][COLOR=#dcdcaa]Where-Object[/COLOR][COLOR=#d4d4d4] { -not [[/COLOR][COLOR=#569cd6]String[/COLOR][COLOR=#d4d4d4]]::IsNullOrWhiteSpace([/COLOR][COLOR=#9cdcfe]$_[/COLOR][COLOR=#d4d4d4]) } |[/COLOR]
[COLOR=#d4d4d4]                [/COLOR][COLOR=#dcdcaa]Add-Content[/COLOR][COLOR=#d4d4d4] E:\Temp\newFile.json[/COLOR]
[COLOR=#d4d4d4]    }[/COLOR]

Works on a little test file I made. YMMV.

 

[SimonMac]

Might be your edit but that's not valid JSON.

As you can't have line breaks in a property, you should be able to treat the 'geometry' line as a single line.

Because it's a large file, you don't want to read it all into memory in one go so this will do 5000 lines at a time:

[COLOR=#9cdcfe]$batchSize[/COLOR][COLOR=#d4d4d4] = [/COLOR][COLOR=#b5cea8]5000[/COLOR]

[COLOR=#dcdcaa]Get-Content[/COLOR][COLOR=#d4d4d4] E:\bigFile.json -ReadCount [/COLOR][COLOR=#9cdcfe]$batchSize[/COLOR][COLOR=#d4d4d4] |[/COLOR]
[COLOR=#d4d4d4] [/COLOR][COLOR=#dcdcaa]Foreach-Object[/COLOR][COLOR=#d4d4d4] {[/COLOR]
[COLOR=#d4d4d4] [/COLOR][COLOR=#9cdcfe]$_[/COLOR][COLOR=#d4d4d4] -replace [/COLOR][COLOR=#ce9178]"^\W*[/COLOR][COLOR=#d7ba7d]`"[/COLOR][COLOR=#ce9178]geometry[/COLOR][COLOR=#d7ba7d]`"[/COLOR][COLOR=#ce9178].+,$"[/COLOR][COLOR=#d4d4d4] |[/COLOR]
[COLOR=#d4d4d4] [/COLOR][COLOR=#dcdcaa]Where-Object[/COLOR][COLOR=#d4d4d4] { -not [[/COLOR][COLOR=#569cd6]String[/COLOR][COLOR=#d4d4d4]]::IsNullOrWhiteSpace([/COLOR][COLOR=#9cdcfe]$_[/COLOR][COLOR=#d4d4d4]) } |[/COLOR]
[COLOR=#d4d4d4] [/COLOR][COLOR=#dcdcaa]Add-Content[/COLOR][COLOR=#d4d4d4] E:\Temp\newFile.json[/COLOR]
[COLOR=#d4d4d4] }[/COLOR]

Works on a little test file I made. YMMV.

As Eek will attest I am not a code monkey, what type of script is that? shell?

 

[eek]

Based on fulcon's regular expression.

Open the file up in Notepad++ and use the following search and replace

Replace the *, with *} if there isn't a , at the end of the geometry line but there should be.

 

[fulcon]

As Eek will attest I am not a code monkey, what type of script is that? shell?

It's Windows PowerShell. May work on Mac or Linux if you have PowerShell installed but I can't test that.

 

[NickFitz]

Might be your edit but that's not valid JSON.

As you can't have line breaks in a property, you should be able to treat the 'geometry' line as a single line.

Because it's a large file, you don't want to read it all into memory in one go so this will do 5000 lines at a time:

[COLOR=#9cdcfe]$batchSize[/COLOR][COLOR=#d4d4d4] = [/COLOR][COLOR=#b5cea8]5000[/COLOR]

[COLOR=#dcdcaa]Get-Content[/COLOR][COLOR=#d4d4d4] E:\bigFile.json -ReadCount [/COLOR][COLOR=#9cdcfe]$batchSize[/COLOR][COLOR=#d4d4d4] |[/COLOR]
[COLOR=#d4d4d4] [/COLOR][COLOR=#dcdcaa]Foreach-Object[/COLOR][COLOR=#d4d4d4] {[/COLOR]
[COLOR=#d4d4d4] [/COLOR][COLOR=#9cdcfe]$_[/COLOR][COLOR=#d4d4d4] -replace [/COLOR][COLOR=#ce9178]"^\W*[/COLOR][COLOR=#d7ba7d]`"[/COLOR][COLOR=#ce9178]geometry[/COLOR][COLOR=#d7ba7d]`"[/COLOR][COLOR=#ce9178].+,$"[/COLOR][COLOR=#d4d4d4] |[/COLOR]
[COLOR=#d4d4d4] [/COLOR][COLOR=#dcdcaa]Where-Object[/COLOR][COLOR=#d4d4d4] { -not [[/COLOR][COLOR=#569cd6]String[/COLOR][COLOR=#d4d4d4]]::IsNullOrWhiteSpace([/COLOR][COLOR=#9cdcfe]$_[/COLOR][COLOR=#d4d4d4]) } |[/COLOR]
[COLOR=#d4d4d4] [/COLOR][COLOR=#dcdcaa]Add-Content[/COLOR][COLOR=#d4d4d4] E:\Temp\newFile.json[/COLOR]
[COLOR=#d4d4d4] }[/COLOR]

Works on a little test file I made. YMMV.

It looks like the original file is GeoJSON, so the geometry property is an object and a simple regex removing single lines isn't going to work.

 

[TheDude]