New Feature Select ... as JSON

(1) By dCipher on 2022-03-18 18:55:07 [link] [source]

I would like to suggest a new feature that would allow the result of any query to be returned as a JSON array of objects. Obviously each object would represent a row in the query and the column name would be the JSON name and the column would be the JSON value. The result would be one row with one column called JSON by default.

Example: select * from myTable where product in ('chips','pepsi') as JSON

JSON Column Value:

[{"product":"chips","price":2.00,"qty":4}, {"product":"pepsi","price":2.99,"qty":1}]

Thanks Simon

(2.3) By midijohnny on 2022-03-18 20:25:24 edited from 2.2 in reply to 1 [source]

Just to add (I haven't tried this - but just looking at the docs).

There is a precedence of this in MS SQL

But they use a 'FOR JSON [options]' construct like this:

SELECT name, surname  
FROM emp  
FOR JSON AUTO;

Sample output:

[{
    "name": "John"
}, {
    "name": "Jane",
    "surname": "Doe"
}]

Postgresql has the 'row_to_json' function (again - I haven't tried this,just quoting the docs):

[...]
row_to_json(row(1,'foo'))

Example output (presumably from a different input that the one given above)

{"f1":1,"f2":"foo"}

I'm not 100% sure, but I believe the 'row' function here is equivalent to to (values(1,'foo')).

(3) By dCipher on 2022-03-18 20:59:01 in reply to 2.3 [link] [source]

Postgres does work this way as I use it all the time to retrieve table rows as Json arrays.

(4) By Bjoern Hoehrmann (bjoern) on 2022-03-23 02:05:43 in reply to 1 [link] [source]

(5.1) By bokwoon on 2022-03-25 10:05:43 edited from 5.0 in reply to 1 [link] [source]

I am curious what is your use case for this, because json_group_array(json_object(...)) can already convert SQL rows to a JSON array (provided you know the columns ahead of time)

WITH myTable (product, price, qty) AS (
    VALUES ('chips', 2.00, 4), ('pepsi', 2.99, 1)
)
SELECT
    json_group_array(json_object('product', product, 'price', price, 'qty', qty))
FROM
    myTable
;

[{"product":"chips","price":2.00,"qty":4},{"product":"pepsi","price":2.99,"qty":1}]

(7) By dCipher on 2022-04-26 01:09:19 in reply to 5.1 [link] [source]

The main problem is that if the table has more than 63 columns then you exceed the limits of the allowed number of parameters for a function call. In my case I have a table with over 100 columns and I need all the columns returned.

Simon

(8.1) By Aask (AAsk1902) on 2024-02-20 11:37:17 edited from 8.0 in reply to 5.1 [link] [source]

What is the SQL that will reverse this technique, that is, take

[{"product":"chips","price":2.00,"qty":4},{"product":"pepsi","price":2.99,"qty":1}]

as input and return tabular data?

Does the limit mentioned here still apply to the current version of SQLite (3.45.1)?

(9) By Spindrift (spindrift) on 2024-02-20 15:45:04 in reply to 8.1 [link] [source]

Do you mean these json table valued functions?

You'll need to break them out further to either add the data into a table or analyse it.

(6) By anonymous on 2022-03-25 15:57:39 in reply to 1 [link] [source]

I think it would be better to be implemented in an extension instead. (However, the extensions cannot affect the syntax much, though)