Menu
I just had the idea of writing a function in MySQL that I can pass a subquery to for it to output the JSON representation of that subquery.
I have quite a lot of data that I often fetch from MySQL, then convert it to JSON for API output. Could it perhaps be a speed increase to write a MySQL function to do this on the SQL server that just returns the JSON?
My imagination:
query('SELECT * FROM people');
// Output:
// +----+--------+-----+
// | id | name | Age |
// +----+--------+-----+
// | 1 | Molly | 24 |
// | 2 | Edward | 28 |
// +----+--------+-----+
query('JSON(SELECT * FROM people)');
// Output:
// [{"id":1,"name":"Molly","Age":24},{"id":2,"name":"Edward","Age":28}]
Possible? If yes, any clues to how I can start?
671
The output of the FOR JSON clause is of type NVARCHAR(MAX), so you can assign it to any variable, as shown in the following example. Use FOR JSON output in SQL Server user-defined functions. You can create user-defined functions that format result sets as JSON and return this JSON output.
JSON_QUERY ( JSON string, path) Used to extract an array of data or objects from the JSON string. 4. JSON_MODIFY: There is an option called “JSON_MODIFY” in (Transact-SQL) function is available to update the value of a property in a JSON string and return the updated JSON string. Whenever there is a requirement to change JSON text, we can do that
Here are few examples to understand how json file format can be used in SQL. Note : Parsing or reading data from JSON file format varies across the relational database servers. For example, in order to parse data in SQL Server 2008 and above, we use OPENJSON function which transforms a JSON array to a table.
Writing SQL query results to a JSON file is as simple as writing them to a CSV file. But it involves one more step. Perform steps 1 through 5, as listed in the CSV file instructions above. Then, add the clause “FOR JSON PATH” to the end of the query, as shown in the code below. Continue with steps 5 and 6.
First, look at this thread (SQL Server) on StackOverflow.
You can also see here for PL/JSON, and here for sql2json (PHP).
193
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
