tjson supports manipulation commands even though there is no create command (I will add it). Here is how your example looks like with tjson with the existing commands:
set sql "select user_id, username, last_visit from users"
set json_arr [::tjson::parse {[]}]
db_foreach get_users $sql {
::tjson::add_item_to_array $json_arr \
[list M \
[list \
user_id [list N $user_id] \
username [list S $username] \
last_visit [list S $last_visit]]]
}
doc_return 200 application/json [::tjson::to_pretty_json $json_arr]
ad_script_abort
And here is what you get:
[
{
"user_id": 0,
"username": "guest",
"last_visit": ""
},
{
"user_id": 759,
"username": "test",
"last_visit": "2023-07-26 09:27:37.590777-04"
}
]
M means it is an object, N means it is a number, and S means it is a string. tjson also supports the triple notation that is used in mongodb driver (documentation for that feature is still pending).