{"id":24496,"date":"2022-03-21T05:07:45","date_gmt":"2022-03-20T19:07:45","guid":{"rendered":"https:\/\/database.guide\/?p=24496"},"modified":"2022-03-22T08:40:17","modified_gmt":"2022-03-21T22:40:17","slug":"sqlite-json_object-function","status":"publish","type":"post","link":"https:\/\/database.guide\/sqlite-json_object-function\/","title":{"rendered":"SQLite JSON_OBJECT()"},"content":{"rendered":"\n

The SQLite json_object()<\/code> function returns a well-formed JSON object based on its arguments.<\/p>\n\n\n\n

The function accepts zero or more pairs of arguments and returns a well-formed JSON object based on those arguments.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n
json_object(label1,value1,...)<\/code><\/pre>\n\n\n\n
Where label1, value2, ...<\/code> represents the label\/value pairs.<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s a simple example to demonstrate:<\/p>\n\n\n\n
SELECT json_object( \"name\", \"Igor\", \"age\", 35 );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\"name\":\"Igor\",\"age\":35}<\/pre>\n\n\n\n
Passing JSON Objects<\/h2>\n\n\n\n
Passing an argument with SQL type TEXT results in a quoted JSON string, with any quoted labels being escaped:<\/p>\n\n\n\n
SELECT json_object( \"user\", '{ \"name\" : \"igor\", \"age\" : 35 }' );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\"user\":\"{ \\\"name\\\" : \\\"igor\\\", \\\"age\\\" : 35 }\"}<\/pre>\n\n\n\n
If we don’t want this to happen, we can use the json()<\/a><\/code> function to provide the value as a valid JSON document:<\/p>\n\n\n\n
SELECT json_object( \"user\", json('{ \"name\" : \"igor\", \"age\" : 35 }') );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\"user\":{\"name\":\"igor\",\"age\":35}}<\/pre>\n\n\n\n
Another way to do it is to use SQLite’s -><\/code> operator<\/a>:<\/p>\n\n\n\n
SELECT json_object( \"user\", '{ \"name\" : \"igor\", \"age\" : 35 }' -> '$' );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\"user\":{\"name\":\"igor\",\"age\":35}}<\/pre>\n\n\n\n
Alternatively, we can use another json_object()<\/code> function:<\/p>\n\n\n\n
SELECT json_object( \"user\", json_object( \"name\", \"Igor\", \"age\", 35 ) );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\"user\":{\"name\":\"Igor\",\"age\":35}}<\/pre>\n\n\n\n
Here it is with some other values:<\/p>\n\n\n\n
SELECT json_object( \n    \"a\", 1, \n    \"user\", json_object( \"name\", \"Igor\", \"age\", 35 ), \n    \"b\", 2 \n    );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\"a\":1,\"user\":{\"name\":\"Igor\",\"age\":35},\"b\":2}<\/pre>\n\n\n\n
Passing JSON Arrays<\/h2>\n\n\n\n
It’s a similar thing when passing JSON arrays:<\/p>\n\n\n\n
SELECT json_object( \"scores\", '[ 9, 4, 7 ]' );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\"scores\":\"[ 9, 4, 7 ]\"}<\/pre>\n\n\n\n
In this case, the value is a string that resembles an array.<\/p>\n\n\n\n
To return an actual JSON array, we can pass our argument to the json()<\/code> function:<\/p>\n\n\n\n
SELECT json_object( \"scores\", json('[ 9, 4, 7 ]') );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\"scores\":[9,4,7]}<\/pre>\n\n\n\n
We can also use the -><\/code> operator:<\/p>\n\n\n\n
SELECT json_object( \"scores\", '[ 9, 4, 7 ]' -> '$' );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\"scores\":[9,4,7]}<\/pre>\n\n\n\n
Alternatively, we can pass the values to the json_array()<\/a><\/code> function:<\/p>\n\n\n\n
SELECT json_object( \"scores\", json_array( 9, 4, 7 ) );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\"scores\":[9,4,7]}<\/pre>\n\n\n\n
Here it is with some other name\/value pairs:<\/p>\n\n\n\n
SELECT json_object( \n    \"name\", \"Bruno\", \n    \"scores\", json_array( 9, 4, 7 ), \n    \"age\", 25 \n    );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\"name\":\"Bruno\",\"scores\":[9,4,7],\"age\":25}<\/pre>\n\n\n\n
Create an Empty Object<\/h2>\n\n\n\n
Calling json_object()<\/code> without passing any arguments results in an empty object:<\/p>\n\n\n\n
SELECT json_object();<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{}<\/pre>\n\n\n\n
Duplicate Labels<\/h2>\n\n\n\n
At the time of writing, json_object()<\/code> accepts duplicate labels without issue. Therefore, we can do stuff like this:<\/p>\n\n\n\n
SELECT json_object( \"b\", 1, \"b\", 2 );<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\"b\":1,\"b\":2}<\/pre>\n\n\n\n
But SQLite’s documentation advises that this may not always be the case – future versions of SQLite may not support such scenarios.<\/p>\n\n\n\n
Database Example<\/h2>\n\n\n\n
We can use json_object()<\/code> when retrieving data from a database. <\/p>\n\n\n\n
Suppose we run the following query:<\/p>\n\n\n\n
SELECT * FROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+-------+---------+--------+\n| PetId | PetName | TypeId |\n+-------+---------+--------+\n| 1     | Homer   | 3      |\n| 2     | Yelp    | 1      |\n| 3     | Fluff   | 2      |\n| 4     | Brush   | 4      |\n+-------+---------+--------+<\/pre>\n\n\n\n
We can see that there are three columns, and we can see their names.<\/p>\n\n\n\n
We can use the json_object()<\/code> function on that table like this:<\/p>\n\n\n\n
SELECT json_object( \n    'PetId', PetId, \n    'PetName', PetName,\n    'TypeId', TypeId \n    )\nFROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
{\"PetId\":1,\"PetName\":\"Homer\",\"TypeId\":3}\n{\"PetId\":2,\"PetName\":\"Yelp\",\"TypeId\":1}\n{\"PetId\":3,\"PetName\":\"Fluff\",\"TypeId\":2}\n{\"PetId\":4,\"PetName\":\"Brush\",\"TypeId\":4}<\/pre>\n\n\n\n
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. <\/p>\n\n\n\n
We can also use the json_group_array()<\/a><\/code> function to encase the documents in an array:<\/p>\n\n\n\n
SELECT json_group_array( \n        json_object(\n        'PetId', PetId, \n        'PetName', PetName,\n        'TypeId', TypeId \n        )\n    )\nFROM Pets;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
[{\"PetId\":1,\"PetName\":\"Homer\",\"TypeId\":3},{\"PetId\":2,\"PetName\":\"Yelp\",\"TypeId\":1},{\"PetId\":3,\"PetName\":\"Fluff\",\"TypeId\":2},{\"PetId\":4,\"PetName\":\"Brush\",\"TypeId\":4}]<\/pre>\n","protected":false},"excerpt":{"rendered":"
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.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[100],"tags":[93,80,20],"class_list":["post-24496","post","type-post","status-publish","format-standard","hentry","category-sqlite","tag-functions","tag-json","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/24496","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=24496"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/24496\/revisions"}],"predecessor-version":[{"id":24533,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/24496\/revisions\/24533"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=24496"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=24496"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=24496"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}