json-sql_2.12

Allows to run SQL over JSON

License

License

Categories

Categories

JSON Data
GroupId

GroupId

com.landoop
ArtifactId

ArtifactId

json-sql_2.12
Last Version

Last Version

2.0.0
Release Date

Release Date

Type

Type

jar
Description

Description

json-sql_2.12
Allows to run SQL over JSON
Project URL

Project URL

https://github.com/landoop/json-sql
Source Code Management

Source Code Management

https://github.com/landoop/json-sql.git

Download json-sql_2.12

How to add to project

<!-- https://jarcasting.com/artifacts/com.landoop/json-sql_2.12/ -->
<dependency>
    <groupId>com.landoop</groupId>
    <artifactId>json-sql_2.12</artifactId>
    <version>2.0.0</version>
</dependency>
// https://jarcasting.com/artifacts/com.landoop/json-sql_2.12/
implementation 'com.landoop:json-sql_2.12:2.0.0'
// https://jarcasting.com/artifacts/com.landoop/json-sql_2.12/
implementation ("com.landoop:json-sql_2.12:2.0.0")
'com.landoop:json-sql_2.12:jar:2.0.0'
<dependency org="com.landoop" name="json-sql_2.12" rev="2.0.0">
  <artifact name="json-sql_2.12" type="jar" />
</dependency>
@Grapes(
@Grab(group='com.landoop', module='json-sql_2.12', version='2.0.0')
)
libraryDependencies += "com.landoop" % "json-sql_2.12" % "2.0.0"
[com.landoop/json-sql_2.12 "2.0.0"]

Dependencies

compile (6)

Group / Artifact Type Version
org.scala-lang : scala-library jar 2.12.8
com.typesafe.scala-logging : scala-logging_2.12 jar 3.9.2
com.landoop : sql-core_2.12 jar 2.0
com.fasterxml.jackson.core : jackson-databind jar 2.10.1
com.fasterxml.jackson.module : jackson-module-scala_2.12 jar 2.10.1
org.json4s : json4s-jackson_2.12 jar 3.6.7

test (7)

Group / Artifact Type Version
org.mockito : mockito-core jar 2.12.0
org.scalacheck : scalacheck_2.12 jar 1.14.1
org.scalatest : scalatest_2.12 jar 3.0.8
junit : junit jar 4.12
org.apache.curator : curator-test jar 3.1.0
org.powermock : powermock-module-junit4 jar 1.6.5
org.pegdown : pegdown jar 1.1.0

Project Modules

There are no modules declared in this project.

Actions Status

json-sql

This is a small utility library allowing you to translate the shape of a JSON document. Let's say we have the following json (A description of a Pizza):

{
  "ingredients": [
    {
      "name": "pepperoni",
      "sugar": 12.0,
      "fat": 4.4
    },
    {
      "name": "onions",
      "sugar": 1.0,
      "fat": 0.4
    }
  ],
  "vegetarian": false,
  "vegan": false,
  "calories": 98,
  "fieldName": "pepperoni"
}

using the library one can apply to types of queries:

  • to flatten it
  • to retain the structure while cherry-picking and/or rename fields The difference between the two is marked by the withstructure* keyword. If this is missing you will end up flattening the structure. This library is dependant on Apache Calcite library. Providing 'FROM X' is not mandatory; it will be ignored anyway

Let's take a look at the flatten first. There are cases when you are receiving a nested json and you want to flatten the structure while being able to cherry pick the fields and rename them. Imagine we have the following JSON:

{
  "name": "Rick",
  "address": {
    "street": {
      "name": "Rock St"
    },
    "street2": {
      "name": "Sunset Boulevard"
    },
    "city": "MtV",
    "state": "CA",
    "zip": "94041",
    "country": "USA"
  }
}

Applying this SQL like syntax

SELECT 
    name, 
    address.street.*, 
    address.street2.name as streetName2 

the projected new JSON is:

{
  "name": "Rick",
  "name_1": "Rock St",
  "streetName2": "Sunset Boulevard"
}

There are scenarios where you might want to rename fields and maybe reorder them. By applying this SQL like syntax on the Pizza JSON

SELECT 
       name, 
       ingredients.name as fieldName, 
       ingredients.sugar as fieldSugar, 
       ingredients.*, 
       calories as cals 
withstructure

we end up projecting the first structure into this one:

{
  "name": "pepperoni",
  "ingredients": [
    {
      "fieldName": "pepperoni",
      "fieldSugar": 12.0,
      "fat": 4.4
    },
    {
      "fieldName": "onions",
      "fieldSugar": 1.0,
      "fat": 0.4
    }
  ],
  "cals": 98
}

Flatten rules

  • you can't flatten a json containing array fields
  • when flattening and the column name has already been used it will get a index appended. For example if field name appears twice and you don't specifically rename the second instance (name as renamedName) the new json will end up containing: name and name_1

How to use it

import JsonSql._
val json: JsonNode= ...
json.sql("SELECT name, address.street.name as streetName")

As simple as that!

Query Examples

You can find more examples in the unit tests, however here are a few used:

  • flattening
//rename and only pick fields on first level
SELECT calories as C ,vegan as V ,name as fieldName 

//Cherry pick fields on different levels in the structure
SELECT name, address.street.name as streetName 

//Select and rename fields on nested level
SELECT name, address.street.*, address.street2.name as streetName2 
  • retaining the structure
//you can select itself - obviousely no real gain on this
SELECT *  withstructure 

//rename a field 
SELECT *, name as fieldName  withstructure

//rename a complex field
SELECT *, ingredients as stuff  withstructure

//select a single field
SELECT vegan  withstructure

//rename and only select nested fields
SELECT ingredients.name as fieldName, ingredients.sugar as fieldSugar, ingredients.*  withstructure
  • selecting a nested object
//select the nested address object on a person
SELECT address

Release Notes

2.0 (2020-01-01)

  • Update to scala 2.12

0.1 (2017-04-17)

  • first release

1.0.2

  • Support added for selecting nested JSON objects. This can be useful for inserting user defined types (UDTs) in Cassandra, for example.

Building

Requires gradle 5.0 to build.

To build

gradle compile

To test

gradle test

You can also use the gradle wrapper

./gradlew build

To view dependency trees

gradle dependencies # 
com.landoop

Lenses.io

Please visit https://github.com/lensesio for Lenses.io's repositories

Versions

Version
2.0.0