How to Store and Query JSON Objects
You can insert JSON data in SnappyData tables and execute queries on the tables.
Code Example: Loads JSON data from a JSON file into a column table and executes query
The code snippet loads JSON data from a JSON file into a column table and executes the query against it. The source code for JSON example is located at WorkingWithJson.scala. After creating SnappySession, the JSON file is read using Spark API and loaded into a SnappyData table.
Get a SnappySession:
val spark: SparkSession = SparkSession .builder .appName("WorkingWithJson") .master("local[*]") .getOrCreate val snSession = new SnappySession(spark.sparkContext)
Create a DataFrame from the JSON file:
val some_people_path = s"quickstart/src/main/resources/some_people.json" // Read a JSON file using Spark API val people = snSession.read.json(some_people_path) people.printSchema()
Create a SnappyData table and insert the JSON data in it using the DataFrame:
//Drop the table if it exists snSession.dropTable("people", ifExists = true) //Create a columnar table with the Json DataFrame schema snSession.createTable(tableName = "people", provider = "column", schema = people.schema, options = Map.empty[String,String], allowExisting = false) // Write the created DataFrame to the columnar table people.write.insertInto("people")
Append more data from a second JSON file:
// Append more people to the column table val more_people_path = s"quickstart/src/main/resources/more_people.json" //Explicitly passing schema to handle record level field mismatch // e.g. some records have "district" field while some do not. val morePeople = snSession.read.schema(people.schema).json(more_people_path) morePeople.write.insertInto("people") //print schema of the table println("Print Schema of the table\n################") println(snSession.table("people").schema)
Execute queries and return the results
// Query it like any other table val nameAndAddress = snSession.sql("SELECT " + "name, " + "address.city, " + "address.state, " + "address.district, " + "address.lane " + "FROM people") nameAndAddress.toJSON.show()