Here are two methods for returning the Julian Day in SQLite.
The Julian Day is the fractional number of days since noon in Greenwich on November 24, 4714 B.C. It’s the continuous count of days since the beginning of the Julian period, and is used primarily by astronomers, and in software for easily calculating elapsed days between two events.
Continue readingIn SQLite, the ->> operator extracts a subcomponent from a JSON document and returns an SQL representation of that subcomponent.
The ->> operator was first introduced in SQLite version 3.38.0 (released on 22 February 2022).
In SQLite, the -> operator extracts a subcomponent from a JSON document and returns a JSON representation of that subcomponent.
The -> operator was first introduced in SQLite version 3.38.0 (released on 22 February 2022).
In SQLite, json_tree() is a table-valued function that walks the JSON value provided as its first argument and returns a table consisting of one row for each array element or object member.
We provide the JSON value as an argument when we call the function.
Continue readingIn SQLite, json_each() is a table-valued function that walks the JSON value provided as its first argument and returns a table consisting of one row for each array element or object member.
We provide the JSON value as an argument when we call the function.
Continue readingThe SQLite json_type() function returns the type of the outermost element of the given JSON.
We provide the JSON as an argument when we call the function.
We can optionally pass a path, which allows us to get the type of a specific array element or object member within the JSON.
Continue readingIn SQLite, the json_quote() function converts a number or string into its corresponding JSON representation.
We provide the number or string as an argument when we call the function, and the function returns a JSON representation of that value.
Continue readingThe SQLite command line interface provides us with the ability to auto-generate SQL INSERT statements from a query. This can be done by changing the output mode to insert.
We can use the SQLite json_remove() function to remove one or more elements from a JSON object or array.
We pass the original JSON as the first argument when we call the function, followed by one or more paths that specify which elements to remove. By “elements”, I mean either array elements or object members (key/value pairs).
Continue readingIn SQLite, the json_patch() function can be used to add, modify, or delete elements of a JSON Object.
To do this, it runs the RFC-7396 MergePatch algorithm to apply a given patch against the given JSON input.
We pass the original JSON as the first argument when we call the function, followed by the patch. The function then applies that patch against JSON in the first argument.
Continue reading