The SQLite json_object() function returns a well-formed JSON object based on its arguments.

The function accepts zero or more pairs of arguments and returns a well-formed JSON object based on those arguments.

Syntax

json_object(label1,value1,...)

Where label1, value2, ... represents the label/value pairs.

Example

Here’s a simple example to demonstrate:

SELECT json_object( "name", "Igor", "age", 35 );

Result:

{"name":"Igor","age":35}

Passing JSON Objects

Passing an argument with SQL type TEXT results in a quoted JSON string, with any quoted labels being escaped:

SELECT json_object( "user", '{ "name" : "igor", "age" : 35 }' );

Result:

{"user":"{ \"name\" : \"igor\", \"age\" : 35 }"}

If we don’t want this to happen, we can use the json() function to provide the value as a valid JSON document:

SELECT json_object( "user", json('{ "name" : "igor", "age" : 35 }') );

Result:

{"user":{"name":"igor","age":35}}

Another way to do it is to use SQLite’s -> operator:

SELECT json_object( "user", '{ "name" : "igor", "age" : 35 }' -> '$' );

Result:

{"user":{"name":"igor","age":35}}

Alternatively, we can use another json_object() function:

SELECT json_object( "user", json_object( "name", "Igor", "age", 35 ) );

Result:

{"user":{"name":"Igor","age":35}}

Here it is with some other values:

SELECT json_object( 
    "a", 1, 
    "user", json_object( "name", "Igor", "age", 35 ), 
    "b", 2 
    );

Result:

{"a":1,"user":{"name":"Igor","age":35},"b":2}

Passing JSON Arrays

It’s a similar thing when passing JSON arrays:

SELECT json_object( "scores", '[ 9, 4, 7 ]' );

Result:

{"scores":"[ 9, 4, 7 ]"}

In this case, the value is a string that resembles an array.

To return an actual JSON array, we can pass our argument to the json() function:

SELECT json_object( "scores", json('[ 9, 4, 7 ]') );

Result:

{"scores":[9,4,7]}

We can also use the -> operator:

SELECT json_object( "scores", '[ 9, 4, 7 ]' -> '$' );

Result:

{"scores":[9,4,7]}

Alternatively, we can pass the values to the json_array() function:

SELECT json_object( "scores", json_array( 9, 4, 7 ) );

Result:

{"scores":[9,4,7]}

Here it is with some other name/value pairs:

SELECT json_object( 
    "name", "Bruno", 
    "scores", json_array( 9, 4, 7 ), 
    "age", 25 
    );

Result:

{"name":"Bruno","scores":[9,4,7],"age":25}

Create an Empty Object

Calling json_object() without passing any arguments results in an empty object:

SELECT json_object();

Result:

{}

Duplicate Labels

At the time of writing, json_object() accepts duplicate labels without issue. Therefore, we can do stuff like this:

SELECT json_object( "b", 1, "b", 2 );

Result:

{"b":1,"b":2}

But SQLite’s documentation advises that this may not always be the case – future versions of SQLite may not support such scenarios.

Database Example

We can use json_object() when retrieving data from a database.

Suppose we run the following query:

SELECT * FROM Pets;

Result:

+-------+---------+--------+
| PetId | PetName | TypeId |
+-------+---------+--------+
| 1     | Homer   | 3      |
| 2     | Yelp    | 1      |
| 3     | Fluff   | 2      |
| 4     | Brush   | 4      |
+-------+---------+--------+

We can see that there are three columns, and we can see their names.

We can use the json_object() function on that table like this:

SELECT json_object( 
    'PetId', PetId, 
    'PetName', PetName,
    'TypeId', TypeId 
    )
FROM Pets;

Result:

{"PetId":1,"PetName":"Homer","TypeId":3}
{"PetId":2,"PetName":"Yelp","TypeId":1}
{"PetId":3,"PetName":"Fluff","TypeId":2}
{"PetId":4,"PetName":"Brush","TypeId":4}

Here, I explicitly specified the label names, then used the actual columns from the database for the values. Each row becomes a JSON document, and each column becomes a key/value pair in that JSON document.

We can also use the json_group_array() function to encase the documents in an array:

SELECT json_group_array( 
        json_object(
        'PetId', PetId, 
        'PetName', PetName,
        'TypeId', TypeId 
        )
    )
FROM Pets;

Result:

[{"PetId":1,"PetName":"Homer","TypeId":3},{"PetId":2,"PetName":"Yelp","TypeId":1},{"PetId":3,"PetName":"Fluff","TypeId":2},{"PetId":4,"PetName":"Brush","TypeId":4}]