How to Store and Query JSON Objects

You can insert JSON data in SnappyData tables and execute queries on the tables.


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

val snSession = new SnappySession(spark.sparkContext)

Create a DataFrame from the JSON file:

// Read a JSON file using Spark API
val people =

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

Append more data from a second JSON file:

// Append more people to the column table

//Explicitly passing schema to handle record level field mismatch
val morePeople =

//print schema of the table

Execute queries and return the results

// Query it like any other table
val nameAndAddress = snSession.sql("SELECT " +
    "name, " +
    ", " +
    "address.state, " +
    "address.district, " +
    "address.lane " +
    "FROM people")