Working with JSON in multiple database systems

In my role as a BI Dev, I work with many different database systems as well as many different file types. Recently many of the applications that we integrate with to extract data have been storing their data in JSON format. The challenge for us then is to extract the required information for our reports/dashboards. Next, I will be showing you how to extract JSON contents from various database systems.

Given this sample file, we will be looking at how to extract the contents if this data was stored in MySQL, MSSQL, PostgreSQL and BigQuery.

MySQL

To get the orderer email address:

select jsonCol ->> '$.orderer.email'

To get the first customer email address (customers is an array here in case you didn’t realise):

select jsonCol ->> '$.customers[0].email'

To get the second customer email address:

select jsonCol ->> '$.customers[1].email'

To get the email addresses of ALL of the customers:

the numbers table simply contains a list of numbers from 0 to 1000

MSSQL

To get the orderer email address:

select json_value(jsonCol,'$.orderer.email')

To get the first customer email address (customers is an array here in case you didn’t realise):

select json_value(jsonCol,'$.customers[0].email')

To get the second customer email address:

select json_value(jsonCol,'$.customers[1].email')

PostgreSQL

To get the orderer email address:

select jsonCol::json -> 'orderer' ->> 'email'

To get the first customer email address (customers is an array here in case you didn’t realise):

select jsonCol::json -> 'customers' -> 0 ->> 'email'

To get the second customer email address:

select jsonCol::json -> 'customers' -> 1 ->> 'email'

BigQuery

In BigQuery, JSON data may be stored in two ways:

  • In a column of type “RECORD”: This data type is specifically designed to store nested structure data (JSON) in BigQuery.
  • In a column of type “STRING”: The JSON value is treated just like a normal string that happens to have JSON format. Let’s call this “stringified JSON”.

Each scenario needs to be handled differently as follow.

To get the orderer email address from a RECORD:

select jsonCol.orderer.email

To get the orderer email address from a stringified JSON:

select JSON_EXTRACT(jsonCol, '$.orderer.email')

To get the first customer email address from customers JSON array from a RECORD:

SELECT c.email
FROM tableWithJSONfield t
LEFT JOIN UNNEST(customers) as c
limit 1

To get the first customer email address from customers JSON array from a stringified JSON:

select JSON_EXTRACT(jsonCol, '$.customers[0].email')

To get the second customer email address from a RECORD:

SELECT c.email
FROM tableWithJSONfield t
LEFT JOIN UNNEST(customers) as c
limit 1 offset 1

To get the second customer email address from a stringified JSON:

select JSON_EXTRACT(jsonCol, '$.customers[1].email')

To get the email address of ALL of the customers from a RECORD:

SELECT c.email
FROM tableWithJSONfield t
LEFT JOIN UNNEST(customers) as c

To get the email address of ALL of the customers from a stringified JSON:

SELECT JSON_EXTRACT(c, '$.email')
FROM tableWithJSONfield t
left join unnest(json_extract_array(jsonCol)) as j
left join unnest(json_extract_array(j, '$.customers')) as c

--

--

--

Full time BI Developer. Part time Designer/Crafter/Reader

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Important System calls every programmer should know

How to publish Helm 3 charts to GitHub Container Registry using GitHub Actions

enable improved container support on GitHub

Agile in the real world — no rules?

An example of the SQLite Database (Part 2)

Web2 vs Web3

Install windows 10/7 on mac using virtualbox

Avoiding Rookie CSS Mistakes: The Basics

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Cathlyn

Cathlyn

Full time BI Developer. Part time Designer/Crafter/Reader

More from Medium

MySQL VS MongoDB

Specification first driven API development: consumer protection use-case

The simplest backend for your application.

Relational Database working in 5 minutes