SQLite JSON_ARRAY()

In SQLite, the json_array() function returns a well-formed JSON array based on its arguments.

The function accepts zero or more arguments, and each argument becomes an element in the resulting array.

Syntax

json_array(value1,value2,...)

Where value1, value2, ... represents the array elements for the resulting array.

Here’s a simple example to demonstrate:

SELECT json_array( 9, 7, 12, 11 );

Result:

[9,7,12,11]

Adding quotes to a number results in that array element being a quoted JSON string:

SELECT json_array( 9, '7', 12, 11 );

Result:

[9,"7",12,11]

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

SELECT json_array( '{ "a" : 1 }' );

Result:

["{ \"a\" : 1 }"]

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_array( json('{ "a" : 1 }') );

Result:

[{"a":1}]

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

SELECT json_array( '{ "a" : 1 }' -> '$' );

Result:

[{"a":1}]

Alternatively, we can use the json_object() function:

SELECT json_array( json_object( 'a', 1 ) );

Result:

[{"a":1}]

Here it is with some other array elements:

SELECT json_array( "Cat", json_object( 'a', 1 ), "Dog" );

Result:

["Cat",{"a":1},"Dog"]

It’s a similar thing when passing JSON arrays:

SELECT json_array( '[ 9, 4, 7 ]' );

Result:

["[ 9, 4, 7 ]"]

In this case, we get a JSON array that contains a string that resembles another array.

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

SELECT json_array( json('[ 9, 4, 7 ]') );

Result:

[[9,4,7]]

We can also use the -> operator:

SELECT json_array( '[ 9, 4, 7 ]' -> '$' );

Result:

[[9,4,7]]

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

SELECT json_array( json_array( 9, 4, 7 ) );

Result:

[[9,4,7]]

Here it is with some other array elements:

SELECT json_array( "Cat", json_array( 9, 4, 7 ), "Dog" );

Result:

["Cat",[9,4,7],"Dog"]

Calling json_array() without passing any arguments results in an empty array:

SELECT json_array();

Result:

[]