N.B.: I am primarily familiar with Microsoft's dialect (regrettably).
Documented here are some crimes against humanity.
Generating JSON #
No fancy JSON functions in my version of SQL Server :(
CREATE TYPE [dbo].[JsonifiableData] AS TABLE(
[JsonPath] [varchar](max) NOT NULL,
[JsonValue] [varchar](max) NULL,
[JsonType] [varchar](max) NOT NULL DEFAULT ('string')
)
GO
CREATE FUNCTION [dbo].[fn_JsonifyData] (@data JsonifiableData READONLY)
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @accumulator JsonifiableData
-- "Base case", there is no subpath (ie. no 'dot's left)
INSERT INTO @accumulator (JsonPath, JsonValue, JsonType)
SELECT
JsonPath,
CASE JsonType
WHEN 'string' THEN CONCAT('"', REPLACE(REPLACE(REPLACE(JsonValue, '''', '\'''), CHAR(10), '\n'), CHAR(13), '\r'), '"')
WHEN 'date' THEN CONCAT('"', FORMAT(CAST(JsonValue AS DATETIME), 'O'), '"')
WHEN 'array' THEN 'Arrays are not supported (lol)'
ELSE JsonValue
END,
'JSON'
FROM @data
WHERE CHARINDEX('.', JsonPath) = 0
-- Treat NULL in this table as an "undefined" property, so don't send it.
AND JsonValue IS NOT NULL
/* Recursive case(s), where there is some child paths.
* We group on the child path in case there are two things
* that should go to the same child object */
DECLARE curs cursor FOR
SELECT SUBSTRING(JsonPath, 0, CHARINDEX('.', JsonPath))
FROM @data
GROUP BY SUBSTRING(JsonPath, 0, CHARINDEX('.', JsonPath))
HAVING SUBSTRING(JsonPath, 0, CHARINDEX('.', JsonPath)) <> ''
OPEN curs
DECLARE @path VARCHAR(MAX)
DECLARE @childData JsonifiableData
FETCH NEXT FROM curs INTO @path
WHILE @@FETCH_STATUS = 0 BEGIN
DELETE FROM @childData
INSERT INTO @childData
SELECT
SUBSTRING(JsonPath, LEN(@path) + 2, LEN(JsonPath)),
JsonValue,
JsonType
FROM @data
WHERE CHARINDEX(@path + '.', JsonPath) = 1
INSERT INTO @accumulator (JsonPath, JsonValue, JsonType)
VALUES (@path, dbo.fn_JsonifyData(@childData), 'JSON')
FETCH NEXT FROM curs INTO @path
END
CLOSE curs
DEALLOCATE curs
DECLARE @result VARCHAR(MAX)
SELECT @result = '{' + dbo.af_String_Agg(CONCAT('"', JsonPath, '": ', JsonValue), ', ') + '}'
FROM @accumulator
RETURN @result
END
I haven't implemented arrays yet because I didn't need them.
Also afStringAgg
is a gross custom CLR function since I also didn't have string_agg.
If you don't either you could get away with just setting @result = @result + CONCAT(...)
Helpful kinda-declaritive-y way of converting a wide table into a tall table as required by this function:
DECLARE @tbl JsonifiableData
INSERT INTO @tbl (JsonPath, JsonValue)
SELECT P.*
FROM someTable
OUTER APPLY (VALUES
('example.nesting.a', someTable.a),
('example.nesting.b', someTable.b),
('example.c', someTable.c)
) P(JsonPath, JsonValue)
WHERE id = x
Generating Dates #
SELECT DATEFROMPARTS(yearr, monthh, dayy) datee
FROM (VALUES (1900),(1901),(1902),(1903),(1904),(1905),(1906),(1907),(1908),(1909),(1910),(1911),(1912),(1913),(1914),(1915),(1916),(1917),(1918),(1919),(1920),(1921),(1922),(1923),(1924),(1925),(1926),(1927),(1928),(1929),(1930),(1931),(1932),(1933),(1934),(1935),(1936),(1937),(1938),(1939),(1940),(1941),(1942),(1943),(1944),(1945),(1946),(1947),(1948),(1949),(1950),(1951),(1952),(1953),(1954),(1955),(1956),(1957),(1958),(1959),(1960),(1961),(1962),(1963),(1964),(1965),(1966),(1967),(1968),(1969),(1970),(1971),(1972),(1973),(1974),(1975),(1976),(1977),(1978),(1979),(1980),(1981),(1982),(1983),(1984),(1985),(1986),(1987),(1988),(1989),(1990),(1991),(1992),(1993),(1994),(1995),(1996),(1997),(1998),(1999),(2000),(2001),(2002),(2003),(2004),(2005),(2006),(2007),(2008),(2009),(2010),(2011),(2012),(2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020),(2021),(2022),(2023),(2024),(2025),(2026),(2027),(2028),(2029),(2030),(2031),(2032),(2033),(2034),(2035),(2036),(2037),(2038),(2039),(2040),(2041),(2042),(2043),(2044),(2045),(2046),(2047),(2048),(2049),(2050),(2051),(2052),(2053),(2054),(2055),(2056),(2057),(2058),(2059),(2060),(2061),(2062),(2063),(2064),(2065),(2066),(2067),(2068),(2069),(2070),(2071),(2072),(2073),(2074),(2075),(2076),(2077),(2078),(2079),(2080),(2081),(2082),(2083),(2084),(2085),(2086),(2087),(2088),(2089),(2090),(2091),(2092),(2093),(2094),(2095),(2096),(2097),(2098),(2099),(2100)) years(yearr)
CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) months(monthh)
JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31)) days(dayy)
ON ISDATE(CONCAT(yearr, '-', monthh, '-', dayy)) = 1
Note that I have never actually been able to use this in production because when using it in joins TSQL was somehow doing the DATEFROMPARTS
before the ISDATE
, causing DATEFROMPARTS
to throw :(.
If you need more years seq 1900 2100 | tr '\n' ',' | sed 's/\([0-9]*\),/(\1),/g'
;)
SEE-ALSO: Generate a set or sequence without loops