Better way to convert DB::ResultSet to JSON when database schema is unknown

Help & Support
1

Hi
The code below will do the job but is there a better way to convert DB::ResultSet to JSON when database schema is unknown?

require "sqlite3"
require "db"
require "json"

db =DB.open("sqlite3://luckdb")
result_array =[] of DB::Any
column_names =[] of String
db.query_all("SELECT * FROM movie") do |rs|
    column_names =rs.column_names
    rs.column_names.each do
        result_array << rs.read
    end
end
i = 0
j = 0
result = [] of JSON::Any
(result_array.size/column_names.size).to_i32.times do
    result_json = JSON.build do |json|
        json.object do         
                column_names.size.times do
                    json.field column_names[i],find_type(result_array[j])
                    i+=1
                    j+=1
                end  
        end
    end        
    i =0
    result <<(JSON.parse(result_json))
end
def find_type(value : DB::Any)
    value =value.to_s
    begin
        value.to_f64
    rescue exception
        case value
        when "false"
            false
        when "true"
            true
        else
            value
        end
    end
end
p! result.to_json
2

You could try to serialize to JSON directly in the database. I have no experience with SQLite, but it has JSON support and I’m pretty sure that should work.

3

I know some database engine have that feature (for example (json_agg(t) in postgres) but I don’t know if sqlite has that.
But if I want to do that in crystal Is there any plan for reflection support or I should use ‘begin rescue’ to find type at runtime?

4

You don’t need reflection because the DB already knows the type of the columns.

5

Yes if I serialze the data in database, json is a somthing the most database already has.
But if I want to convert data to somthing else like msgpack or flatbuffer or … I should do it in crystal, and I think there is no way to choose value type at runtime (Get the type from db or any other way at runtime)
Because of that I asked about reflection.
(In the situation that schema is not known by developer)

6

There’s type reflection. You can use .class or .is_a?

1 Like
7

For reflection of any object you can check the Reference#to_s implementation for how to iterate ivars in compile-time

For how to generate json from db without allocating too much memory you can check https://manas.tech/blog/2017/01/16/from-a-db-to-json-with-crystal/

1 Like