jsondb.md

JsonDB

Seed data

Table users

[
  {"id": 1,"name": "John","age": 30, "salary": 123.45, "tags": ["tag1", "tag2", "tag3"]},
  {"id": 2,"name": "Frank","age": 40, "salary": 234.56, "tags": ["tag1", "tag2"]},
  {"id": 3,"name": "Mark","age": 50, "salary": 345.67, "tags": ["tag2", "tag3"]}
]

Table tools

[
  {"id": 1, "user_id": 1, "name": "John Tool 1"},
  {"id": 2, "user_id": 1, "name": "John Tool 2"},
  {"id": 3, "user_id": 2, "name": "Frank Tool 1"},
  {"id": 4, "user_id": 2, "name": "Frank Tool 2"}
]

Table parts

[
  {"id": 1, "tool_id": 1, "name": "John Tool 1 Part 1","price":10},
  {"id": 2, "tool_id": 1, "name": "John Tool 1 Part 2","price":10},
  {"id": 3, "tool_id": 2, "name": "John Tool 2 Part 1","price":10},
  {"id": 4, "tool_id": 2, "name": "John Tool 2 Part 2","price":10},
  {"id": 5, "tool_id": 3, "name": "Frank Tool 1 Part 1","price":10}
]

Examples

Simple

SELECT users.name, users.age FROM users

[
  {
    "users.name": "John",
    "users.age": 30
  },
  {
    "users.name": "Frank",
    "users.age": 40
  },
  {
    "users.name": "Mark",
    "users.age": 50
  }
]

Simple subjson

SELECT users.name, users.tags FROM users

[
  {
    "users.name": "John",
    "users.tags": ["tag1", "tag2", "tag3"]
  },
  {
    "users.name": "Frank",
    "users.tags": ["tag1", "tag2"]
  },
  {
    "users.name": "Mark",
    "users.tags": ["tag2", "tag3"]
  }

Name alias

SELECT users.name AS name, users.age AS age FROM users

[
  {
    "name": "John",
    "age": 30
  },
  {
    "name": "Frank",
    "age": 40
  },
  {
    "name": "Mark",
    "age": 50
  }
]

WHERE

SELECT users.name AS name, users.age AS age FROM users WHERE users.age <= 40

[
  {
    "name": "John",
    "age": 30
  },
  {
    "name": "Frank",
    "age": 40
  }
]

WHERE subjson

SELECT users.name, users.tags FROM users WHERE users.tags.0 = "tag1"

[
  {
    "users.name": "John",
    "users.tags": ["tag1", "tag2", "tag3"]
  },
  {
    "users.name": "Frank",
    "users.tags": ["tag1", "tag2"]
  }
]

JOIN

SELECT users.name AS name, tools.name AS tool, parts.name AS part 
FROM users 
    JOIN tools ON tools.user_id = users.id
    JOIN parts ON parts.tool_id = tools.id

[
  {
    "name": "John",
    "tool": "John Tool 1",
    "part": "John Tool 1 Part 1"
  },
  {
    "name": "John",
    "tool": "John Tool 1",
    "part": "John Tool 1 Part 2"
  },
  {
    "name": "John",
    "tool": "John Tool 2",
    "part": "John Tool 2 Part 1"
  },
  {
    "name": "John",
    "tool": "John Tool 2",
    "part": "John Tool 2 Part 2"
  },
  {
    "name": "Frank",
    "tool": "Frank Tool 1",
    "part": "Frank Tool 1 Part 1"
  },
  {
    "name": "Frank",
    "tool": "Frank Tool 2",
    "part": null
  },
  {
    "name": "Mark",
    "tool": null,
    "part": null
  }
]

JOIN with WHERE

SELECT users.name AS name, tools.name AS tool, parts.name AS part 
FROM users 
    JOIN tools ON tools.user_id = users.id
    JOIN parts ON parts.tool_id = tools.id
WHERE tools.id > 2 AND users.age <= 40

[
  {
    "name": "Frank",
    "tool": "Frank Tool 1",
    "part": "Frank Tool 1 Part 1"
  },
  {
    "name": "Frank",
    "tool": "Frank Tool 2",
    "part": null
  }
]