Jsonb access vs jsonb_path access.

Learn how to use jsonb and jsonb_path access to data and understand their difference

25 cells · updated Aug 29, 2021

This is a read-only view. Responses shown were saved when the notebook was published — open it in your own Aidbox to run the cells against live data.

This topic describes how to get any field from jsonb document your database with fhir data.

You fhir data is stored in tables. For now we have no data, so lets load some with rest console.

REST Send

POST /fhir/Patient/$load
Accept: text/yaml
Content-Type: text/yaml

source: 'https://storage.googleapis.com/aidbox-public/synthea/100/Patient.ndjson.gz'

Let's get a patient in fhir format and look at it.

We will need sql console for this. In the table "patient" the resource column is the payload of the entry.

SQL Send

select resource from patient limit 1;

As one can see, the data is nested and consists of combination of {} and [], not only usual numbers and strings.

This format is called json, and PostgreSQL has native support to work with it.

{} - notation stands for a key-value pair, where key is usually a string and value is any type. Aliases: dictionary, hash-map, map, associative.

[] - notation stands for a collection of any elements. Aliases: array, vector, collection.

SQL Send

select 
resource#>>'{gender}' as gender
from patient limit 1;

It seems that it works, but what happened?

1. resource - is a column name where the payload of the patient is stored.

2. #>>'{}' is an operator, that, as official docs state, Gets the JSON object at specified path as text.

3. After we put gender in '{}' we get resource#>>'{gender}', because the gender is {gender: "male"} (in other words gender is a hash-map).

Let's look at a more complex element: identifier of the patient.

Technically identifier is a vector of hashmaps (as you now know is a [{... : ...}, {... : ...}]

SQL Send

select 
resource#>>'{identifier}' 
from patient
where resource#>>'{identifier}' is not null
limit 1;

Let's train our new skill a bit more and grab the first identifier from our patient

SQL Send

select 
resource#>>'{identifier, 0}' as first_identifier
from patient
where resource#>>'{identifier}' is not null
limit 1;

SQL Send

select 
resource#>>'{identifier, 1}' as second_identifier
from patient
where resource#>>'{identifier}' is not null
limit 1;

Let's dig more deep into this identifier and extract it's code and system, that is located inside type and more deep.

SQL Send

select 
  resource#>>'{identifier, 1, type, coding, 0, code}' as second_identifier_code
, resource#>>'{identifier, 1, type, coding, 0, system}' as second_identifier_system
from patient
where resource#>>'{identifier}' is not null
limit 1;

As it seems the part that we write inside '{}' is just a path to a value.

The example above in this case is read as ->

1. take identifier,

2. in the collection, take the element whose order number is 1 (second element, ordering starts from 0 here),

3. take coding

4. take first element

5. take code / system

The identifiers in the above examples are a bit different, first has just "system" and "code", the second one is somewhat more complex.

Given that the elements inside [] (collection, vector) are not guaranteed to be ordered, some robust method to get a target value has to exist.

And indeed it does. It is called jsonb_path.

Let's consider the following example to describe that.

Consider you run an OLAP app (analytic application) and you'd want to count all unique patient SSN's in your system.

To do that you will need to:

1. Understand which system stands for an SSN identifier (for our example it is http://terminology.hl7.org/CodeSystem/v2-0203).

Remember, using indexed notation inside jsonb paths is a dirty solution and an antipattern.

2. Inside a resource, get all identifiers from all patients, where system = "http://terminology.hl7.org/CodeSystem/v2-0203", and extract a 'value' from there.

3. Count distinct values from the resulting set of values.

Lets perform these steps one by one.

SQL Send

select 
  resource#>>'{identifier, 2, type, text}' as identifier_description
, resource#>>'{identifier, 2, type, coding, 0, system}' as identifier_system
from patient
limit 1;

No questions are expected here - nothing should be unfamiliar.

SQL Send

select 
jsonb_path_query_first(
  resource, 
  '$.identifier[*] ? (@.type.coding[*].system == "http://terminology.hl7.org/CodeSystem/v2-0203")')#>>'{value}' as ssn
from patient
where resource#>>'{identifier}' is not null
limit 5;

Function jsonb_path_query_first - Gets the first JSON item returned by JSON path for the specified JSON value. Returns NULL on no results.

It takes two arguments - resource and the path to the value.

'$.identifier[] ? (@.type.coding[].system == "http://terminology.hl7.org/CodeSystem/v2-0203")' - is a path.

It can be read as - take any of the identifier elements -> dig by path to type -> dig to coding -> take any element from coding -> take the element, where system = "http://terminology.hl7.org/CodeSystem/v2-0203".

OK, the element is found, now lets take what we need from it. In our case it is just the 'value' field.

All what is left is calculation of the distinct values. The full query is below.

SQL Send

select count ( distinct
jsonb_path_query_first(
  resource, 
  '$.identifier[*] ? (@.type.coding[*].system == "http://terminology.hl7.org/CodeSystem/v2-0203")')#>>'{value}') as ssn
from patient
where resource#>>'{identifier}' is not null
limit 5;

The amount of actions that you can perform on jsonb is enormous, The official documentation is located here: https://www.postgresql.org/docs/12/functions-json.html