Relation:Import JSON
In the first step, import creates a relation where each value and its path is extracted.
In the second step, create a column id by identifying a row id in the path. Extract it from the path with regexreplace.
In the third step, create a column key for the columns you are interested and extract it from the path with regexreplace.
In the fourth step, project only id, key and text.
In the fifth step, deserialize.
Example
Using the file catalog-sample.json
| path | text |
|---|---|
| 1:title | Data Catalog |
| 1:description | Version 1.0 |
| 1:keyword:1 | catalog |
| 1:modified | 2013-05-09 |
| 1:publisher | US Department of X |
| 1:contactPoint | John Doe |
| 1:mbox | john.doe@agency.gov |
| 1:identifier | 1 |
| 1:accessLevel | public |
| 1:bureauCode:1 | 018:10 |
| 1:programCode:1 | 018:001 |
| 1:accessURL | http://agency.gov/data.json |
| 1:format | application/json |
| 2:title | Public Elementary/Secondary Listing |
| 2:description | The purpose of the CCD nonfiscal surveys is to provide a listing of all schools and agencies providing free public elementary and secondary education, along with basic descriptive statistical information on each school and agency listed. Penalties apply for misuse, seehttp://nces.ed.gov/ncesglobal/data_usage_agreement.asp for more details. |
| id | key | text |
|---|---|---|
| 1 | title | Data Catalog |
| 1 | publisher | US Department of X |
| 1 | format | application/json |
| 2 | title | Public Elementary/Secondary Listing |
| 2 | publisher | US Department of Education |
| 2 | format | text/csv |
| 2 | license | Public Domain |
| 3 | title | Public Elementary/Secondary Other Listing |
| 3 | publisher | US Department of Education |
| 3 | license | Public Domain |
| id | title | publisher | format | license |
|---|---|---|---|---|
| 1 | Data Catalog | US Department of X | application/json | |
| 2 | Public Elementary/Secondary Listing | US Department of Education | text/csv | Public Domain |
| 3 | Public Elementary/Secondary Other Listing | US Department of Education | Public Domain |
See also
' JSON files can be imported and relations can be extraxted from the data.
' In the first step, '''import''' creates a relation where each value and its path is extracted.
' In the second step, create a column '''id''' by identifying a row id in the path. Extract it from the path with '''regexreplace'''.
' In the third step, create a column '''key''' for the columns you are interested and extract it from the path with '''regexreplace'''.
' In the fourth step, project only id, key and text.
' In the fifth step, deserialize.
import "catalog-sample.json"
print 15
select path regex "(title|publisher|format|license)"
extend id regexreplace( path , "(d+).*" , "$1" )
extend key regexreplace( path , "d+:(.*)" , "$1" )
select not (key regex ":" )
project id, key, text
deserialize