SQLite JSON Operators

Summary: in this tutorial, you will learn how to use the SQLite JSON operators including -> and ->> to extract subcomponents of JSON.

Introduction to the SQLite JSON operators

Starting from version 3.38.0, SQLite supports the JSON operators -> and ->> for extracting subcomponents of JSON.

Here’s the syntax of the -> and ->> operators:

json -> path
json ->> pathCode language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify a JSON string or JSONB on the left side of the operator.
Second, specify a JSON path expression on the right side of the operator.

Both -> and ->> operators select subcomponents of JSON specified by the JSON path.

The -> operator returns a JSON representation whereas the ->> operator returns the SQL representation of the subcomponent.

It’s important to notice that the -> and ->> operators are different from the json_extract() function.

The json_extract() function returns JSON value if the subcomponent is a JSON array or object, and SQL value if the subcomponent is JSON null, string, or number.

SQLite JSON operator examples

Let’s take some examples of using the JSON operators.

1) Extracting scalar values

The following example uses the -> operator to extract the name from JSON:

SELECT '{"name": "Joe"}' -> '$.name' name;Code language: SQL (Structured Query Language) (sql)

Output:

name
-----
"Joe"Code language: SQL (Structured Query Language) (sql)

In this example, the JSON path $.name selects the name property of the top-level object of JSON. The returned value is a JSON value denoted by the double quotes (").

The following statement uses the ->> operator to extract the name from JSON:

SELECT '{"name": "Joe"}' ->> '$.name' name;Code language: SQL (Structured Query Language) (sql)

Output:

name
----
JoeCode language: SQL (Structured Query Language) (sql)

The return value is an SQL value that is not surrounded by double quotes (“).

2) Extracting JSON arrays

The following example uses the -> operator to extract a JSON array from JSON:

SELECT
  '{"name": "Jane", "skills": ["SQL","SQLite","Python"]}' -> '$.skills' skills;Code language: SQL (Structured Query Language) (sql)

Output:

skills
-------------------------
["SQL","SQLite","Python"]Code language: SQL (Structured Query Language) (sql)

The return value is a JSON array.

The following example uses the ->> operator to extract an array from JSON:

SELECT
  '{"name": "Jane", "skills": ["SQL","SQLite","Python"]}' ->> '$.skills' skills;Code language: SQL (Structured Query Language) (sql)

Output:

skills
-------------------------
["SQL","SQLite","Python"]Code language: SQL (Structured Query Language) (sql)

The return value is an SQL value of the text type even though it looks like a JSON value.

3) Extracting JSON objects

The following example uses the -> operator to extract an object from JSON:

SELECT '{
   "name": "Jane",
   "phones": {
      "work": "(408)-111-2222",
      "home": "(408)-111-3333"
   }
}' -> '$.phones' phones;Code language: SQL (Structured Query Language) (sql)

Output:

phones
-------------------------------------------------
{"work":"(408)-111-2222","home":"(408)-111-3333"}Code language: SQL (Structured Query Language) (sql)

The extracted value is a JSON object.

The following example uses the ->> operator to extract an object from JSON:

SELECT '{
   "name": "Jane",
   "phones": {
      "work": "(408)-111-2222",
      "home": "(408)-111-3333"
   }
}' ->> '$.phones' phones;Code language: SQL (Structured Query Language) (sql)

Output:

phones
-------------------------------------------------
{"work":"(408)-111-2222","home":"(408)-111-3333"}Code language: SQL (Structured Query Language) (sql)

The extracted value is a text string.

4) Using SQLite JSON operators with table data

First, create a table called user_settings:

CREATE TABLE user_settings(
    id INTEGER PRIMARY KEY,
    settings TEXT NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert rows into the user_settings table:

INSERT INTO user_settings (settings) 
VALUES 
('{"user_id": 1, "preferences": {"theme": "dark", "font_size": 14}, "notifications": {"email": true, "sms": false}, "languages": ["en", "es"]}'),
('{"user_id": 2, "preferences": {"theme": "light", "font_size": 12}, "notifications": {"email": true, "sms": true}, "languages": ["en", "de"]}'),
('{"user_id": 3, "preferences": {"theme": "dark", "font_size": 16}, "notifications": {"email": false, "sms": false}, "languages": ["jp", "cn"]}');Code language: SQL (Structured Query Language) (sql)

Third, use the -> operator to extract user_id from the settings column:

SELECT settings -> '$.user_id' user_id
FROM user_settings;Code language: SQL (Structured Query Language) (sql)

Output:

user_id
-------
1
2
3Code language: SQL (Structured Query Language) (sql)

Fourth, use the -> operator to extract the preferences property:

SELECT settings -> '$.preferences' preferences
FROM user_settings;Code language: SQL (Structured Query Language) (sql)

Output:

preferences
--------------------------------
{"theme":"dark","font_size":14}
{"theme":"light","font_size":12}
{"theme":"dark","font_size":16}Code language: SQL (Structured Query Language) (sql)

Fifth, use the -> operator to extract the languages as a JSON array:

SELECT settings -> '$.languages' languages
FROM user_settings;Code language: SQL (Structured Query Language) (sql)

Output:

languages
-----------
["en","es"]
["en","de"]
["jp","cn"]Code language: SQL (Structured Query Language) (sql)

Sixth, retrieve the user settings whose language has the word en:

SELECT
  settings -> '$.languages' languages
FROM
  user_settings
WHERE
  settings ->> '$.languages' LIKE '%"en"%';Code language: SQL (Structured Query Language) (sql)

Output:

languages
-----------
["en","es"]
["en","de"]Code language: SQL (Structured Query Language) (sql)

Summary

Use the JSON operators including -> and ->> to extract subcomponents of JSON.

Was this tutorial helpful ?