Row to JSON

Moderator: NorbertKrupa

Post Reply
sKwa
Newbie
Newbie
Posts: 11
Joined: Wed Aug 02, 2017 3:12 pm

Row to JSON

Post by sKwa » Sat Aug 26, 2017 12:24 am

Hi!

Just for fun I wrote a function that converts row to JSON format.
Source code is here

Example:

dbadmin=> select * from T;
 id | flag |    date    |               str                
----+------+------------+----------------------------------
  1 | t    | 1970-01-01 | some string
  2 |      | 2017-01-01 | Invalid input syntax for integer
  3 | f    |            | Quick reference.
  4 | t    | 2000-02-28 | 
  5 | t    | 2010-04-01 | 
    | f    | 2012-11-15 | foo bar egg
    |      |            | 
(7 rows)

dbadmin=> select jsonify(*) from T;
                                            jsonify                                            
-----------------------------------------------------------------------------------------------
 { "id" : 1, "flag" : true, "date" : "1970-01-01", "str" : "some string"}
 { "id" : 2, "flag" : null, "date" : "2017-01-01", "str" : "Invalid input syntax for integer"}
 { "id" : 3, "flag" : false, "date" : null, "str" : "Quick reference."}
 { "id" : 4, "flag" : true, "date" : "2000-02-28", "str" : ""}
 { "id" : 5, "flag" : true, "date" : "2010-04-01", "str" : null}
 { "id" : null, "flag" : false, "date" : "2012-11-15", "str" : "foo bar egg"}
 { "id" : null, "flag" : null, "date" : null, "str" : null}
(7 rows)

Supported data types:
  • Boolean
  • All numeric data trypes: INTEGER, FLOAT, NUMERIC
  • TIME, DATE, TIMESTAMP
  • INTERVALS
  • CHAR, VARCHAR, LONG VARCHAR
Limitations/Bugs:
  • JSON row limited to 32Mb length
  • So far strings are not escaped (Im working on it)
  • No TimeZone support so far (Im working on it)
----
Feel free to use.
I appreciate for tests and suggestion.

Post Reply

Return to “Vertica User Defined Functions (UDFs)”