Convert JSON document to SQL

Dont do it!

• First of all, there is no good reason to directly convert a raw JSON document to set of normalized SQL tables
• Many databases support storing JSON documents natively.
  • For example, PostgreSQL has excellent support for storing, indexing and querying JSON using jsonb datatype

Why do it?

• For quick and dirty data analysis of some JSON data
• Standard SQL queries is easy and well understood, most databases have non standard syntax for JSON

Example use case

• The linked file is a JSON document of a list of Users.
• User has some details like Name, City, Age
• Each user has a list of friends and their hobbies.
• SQL makes it easy to answer these types of questions
  • Who has most friends?
  • Which hobby is most popular?
  • Number of friends in a particular city for a person
  • Many more.

Note: All of these can be done with some jq or python scripting. This use case is for someone more familiar with SQL than programming.

How

• Upload or paste your JSON data here
• Optionally set some SQL generation parameters as shown

How it works

Normalize mode (default)

• The converter tries to extract JSON objects to separate tables
  • Every table has a generated integer Primary Key
• Nested objects are linked to parent objects using foreign key constrints

Example

This example JSON containing users, friends and hobbies is converted to set of tables with corresponding insert statements as below

Generated SQL

Execute the generated SQL here SQL

Queries

Who has most friends?

select friend_count, u.name, u.city from 
    tbl_users u inner join 
    (select count(*) as friend_count, tbl_users_fk from tbl_users_friends group by tbl_users_fk) f 
on u.tbl_users_pk=f.tbl_users_fk order by friend_count desc, name, city;

Which hobby is most popular?

select count(*) as hobbies_count, hobbies from tbl_users_friends_hobbies group by hobbies order by hobbies_count desc;