Working with JSON¶

SQLite stores JSON in one of two ways, and it matters which one you have:

JSON text — an ordinary TEXT value that happens to hold JSON. Readable in any tool, and you can see it without decoding anything.
JSONB — a BLOB holding SQLite's own binary form of the same document. It is faster for SQLite to read, since it does not have to parse text every time, but it is a blob: anything that has not decoded it sees only bytes.

Every JSON function accepts either form, so most of what follows works the same whichever you have.

The demo database has one table of each kind:

Table	Column	Stored as
`country_json`	`data`	JSON text, indented across several lines
`country_json`	`data_b`	JSONB — the same document as `data`
`orders_json`	`data`	JSONB — one document per order, with a nested object and an array

country_json exists to compare the two storage forms; orders_json shows the shape JSON is actually useful for.

Tip

You do not have to read JSON as raw text. Right-click a cell and choose JSON Editor to see it laid out, whichever form it is stored in — see Editing table data. To see JSONB in the grid rather than as (blob), turn on Show JSONB as readable JSON.

Reading a value out¶

json_extract takes a path and returns what it finds:

select name,
       json_extract(data, '$.capital')    as capital,
       json_extract(data, '$.population') as population
from country_json;

The ->> operator is usually easier to read, and there are two of them:

select name,
       data ->> '$.capital' as as_text,   -- Brasilia
       data ->  '$.capital' as as_json    -- "Brasilia"
from country_json;

->> returns an ordinary SQL value; -> returns JSON. Note the quotes in the second column: it is still a JSON string. Use ->> when you want to compare, sort or display the value, and -> when you are feeding the result into another JSON function.

Both storage forms answer identically — the only difference is what the column holds:

select name,
       data   ->> '$.capital' as from_text,
       data_b ->> '$.capital' as from_jsonb,
       typeof(data)   as text_type,    -- text
       typeof(data_b) as jsonb_type    -- blob
from country_json
where name = 'Brazil';

Searching, and making it fast¶

A JSON field can be used anywhere an expression can:

select name, data ->> '$.population' as population
from country_json
where data ->> '$.continent' = 'South America'
order by 2 desc;

That reads every row. An expression index fixes it, and the demo database has one:

create index idx_country_json_continent
  on country_json (json_extract(data, '$.continent'));

explain query plan
select name from country_json where json_extract(data, '$.continent') = 'Europe';
-- SEARCH country_json USING INDEX idx_country_json_continent (<expr>=?)

Warning

The query has to spell the expression the same way the index does. json_extract(data,'$.continent') uses the index above; the same test written as data ->> '$.continent' does not, even though it returns the same answer. Pick one spelling and use it in both places.

Working with nested documents¶

orders_json holds one document per order, each with a customer object and an items array:

{
  "orderNo": 1003,
  "saleDate": "1988-04-12 00:00:00.000",
  "customer": { "custNo": 1351, "company": "Sight Diver", "city": "Kato Paphos",
                "state": null, "country": "Cyprus" },
  "shipVia": "UPS", "terms": "FOB", "payment": "Credit", "total": 1250,
  "items": [ { "itemNo": 1, "partNo": 1313, "qty": 5, "discount": 0.0 } ]
}

Reach into a nested object with a longer path, and into an array by position:

select data ->> '$.customer.company' as company,
       data ->> '$.items[0].partNo'  as first_part,
       json_array_length(data, '$.items') as line_count
from orders_json
where orderNo = 1003;

Because it is just an expression, a nested field groups and aggregates like any column:

select data ->> '$.customer.country' as country,
       count(*) as orders,
       round(sum(data ->> '$.total'), 2) as total
from orders_json
group by 1
order by orders desc;

Turning an array back into rows¶

json_each gives one row per element, which is how a document goes back to being relational:

select o.orderNo,
       i.value ->> '$.partNo' as partNo,
       i.value ->> '$.qty'    as qty
from orders_json o, json_each(o.data, '$.items') i
where o.orderNo = 1003;

And once it is rows, it joins to ordinary tables:

select p.Description,
       sum(i.value ->> '$.qty') as units
from orders_json o, json_each(o.data, '$.items') i
join parts p on p.PartNo = i.value ->> '$.partNo'
group by p.Description
order by units desc
limit 10;

json_tree is the same idea but walks the whole document, however deep, giving a fullkey such as $.customer.city or $.items[0].qty:

select fullkey, atom
from orders_json, json_tree(data)
where orderNo = 1003 and atom is not null;

Building JSON from rows¶

The reverse direction uses json_object and json_group_array:

select json_group_array(json_object('name', Name, 'capital', Capital)) as countries
from country
where Continent = 'South America';

jsonb_object and jsonb_group_array are the same functions producing JSONB, which is what you want when the result is going into a blob column. This is how the demo tables themselves were built — the script is Tools/dbdemos_json.sql in the source distribution.

Changing a value¶

json_set adds or replaces, json_insert only adds, json_replace only replaces, and json_remove deletes:

update country_json
   set data = json_set(data, '$.currency', 'BRL', '$.population', 214000000)
 where name = 'Brazil';

For a JSONB column use the jsonb_ form, so the column keeps its storage form:

update country_json
   set data_b = jsonb_set(data_b, '$.currency', 'BRL')
 where name = 'Brazil';

Using plain json_set there would turn the blob into text, and the column would quietly stop being JSONB.

These functions reformat

Every json_* function rebuilds the value it returns, so whatever line breaks and indenting the original had are gone. country_json.data is stored indented across several lines; after a json_set it comes back on one line.

This is why the JSON Editor does not use these functions on stored text: it writes back exactly what you left in the editor, so formatting survives being edited.

Checking what you have¶

json_valid answers without ever raising, which makes it the safe thing to test with — json_extract and json() raise malformed JSON on input they cannot parse:

select json_valid(data)     as is_json_text,   -- 1
       json_valid(data, 4)  as is_jsonb,       -- 0, it is text
       json_valid(data_b, 4) as blob_is_jsonb, -- 1
       json_type(data)      as top_level       -- object
from country_json
where name = 'Brazil';

The second argument selects what to test for: 1 for JSON text, 4 for JSONB. These are the same checks SQLite Expert makes when deciding whether the JSON Editor can open a cell.

json(x) converts either form to text, which is the way to read a JSONB value in a tool that does not decode it for you:

select json(data) from orders_json where orderNo = 1003;