Creating json with RUBY looping through SQL Server table

Question

This is a followup to this question:

Ruby create JSON from SQL Server

I was able to create nested arrays in JSON. But I'm struggling with looping through records and appending a file with each record. Also how would I add a root element just at the top of the json and not on each record. "aaSequences" needs to be at the top just once... I also need a comma between each record.

here is my code so far

require 'pp'
require 'tiny_tds'
require 'awesome_print'
require 'json'

class Document
    def initialize strategy
        @document = strategy

    #load helper functions
    load "helpers_ruby.rb"

    #set environment 'dev', 'qa', or 'production'
    load "envconfig_ruby.rb"
    
    end

    def StartUP
        @document.StartUP
    end

    def getseqrecord
        @document.getseqrecord
    end


end

class GetSqlaaSequence

  def StartUP
    ##system "clear"    ##linux
    system "cls"        ##Windows   

        # create connection to db

    $connReportingDB = createReportingxxSqlConn($ms_sql_host, $ms_sql_user, $ms_sql_password, $ms_sql_dbname)

    ##$currentDateTime = DateTime.now
    ##pp 'def StartUP ran at: '+$currentDateTime.to_s

  end


    def getseqrecord

  
        # get the aaaaSequences data
        @result = $connReportingDB.execute("SELECT 
        [jsonFile]
      ,[id]
      ,[title]
      ,[authorIds]
      ,[name]
      ,[aminoAcids]
      ,[schemaId]
      ,[registryId]
      ,[namingStrategy] 
      FROM tablename      
      ")
    
        $aaSequences = Array.new
        @i = 0

        @result.each do |aaSequence|

    jsonFile = aaSequence['jsonFile']
    id = aaSequence['id']
    title = aaSequence['title']
    authorIds = aaSequence['authorIds']
    name = aaSequence['name']
    aminoAcids = aaSequence['aminoAcids']
    schemaId = aaSequence['schemaId']
    registryId = aaSequence['registryId']
    namingStrategy = aaSequence['namingStrategy']
      
            ##end

            @hash = Hash[
                "jsonFile", jsonFile,
                "id", id,
                "title", title,
                "authorIds", authorIds,
                "name", name,
                "aminoAcids", aminoAcids,
                "schemaId", schemaId,
                "registryId", registryId,
                "namingStrategy", namingStrategy
                    ]
                    
            @filename = jsonFile


jsonFileOutput0 = {:"#{title}" => [{:authorIds => ["#{authorIds}"],:aminoAcids => "#{aminoAcids}",:name => "#{name}",:schemaId => "#{schemaId}",:registryId => "#{registryId}",:namingStrategy => "#{namingStrategy}"}]}

                        
jsonFileOutput = JSON.pretty_generate(jsonFileOutput0)     


File.open(jsonFile,"a") do |f|
  f.write(jsonFileOutput)

####ad the comma between records...Not sure if this is the best way to do it...
# File.open(jsonFile,"a") do |f|
  # f.write(',')  
 # end
 
end


            
            $aaSequences[@i] = @hash                    
            @i = @i + 1
            
            
        ##@createReportingSqlConn.close 
            end
        
        end     
    end

Document.new(GetSqlaaSequence.new).StartUP
  
#get aaSequences and create json files
Document.new(GetSqlaaSequence.new).getseqrecord

here is a sample of the json it creates so far...

{
  "aaSequences": [
    {
      "authorIds": [
        "fff_fdfdfdfd"
      ],
      "aminoAcids": "aminoAcids_data",
      "name": "fdfdfddf-555_1",
      "schemaId": "5555fdfd5",
      "registryId": "5fdfdfdf",
      "namingStrategy": "NEW_IDS"
    }
  ]
}{
  "aaSequences": [
    {
      "authorIds": [
        "fff_fdfdfdfd"
      ],
      "aminoAcids": "aminoAcids_data",
      "name": "fdfdfddf-555_2",
      "schemaId": "5555fdfd5",
      "registryId": "5fdfdfdf",
      "namingStrategy": "NEW_IDS"
    }
  ]
}

and here is an example of what I need it to look like

{
  "aaSequences": [
    {
     "authorIds": [
        "authorIds_data"
      ],
      "aminoAcids": "aminoAcids_data",
      "name": "name_data",
      "schemaId": "schemaId_data",
      "registryId": "registryId_data",
      "namingStrategy": "namingStrategy_data"
    },
    {
     "authorIds": [
        "authorIds_data"
      ],
      "aminoAcids": "aminoAcids_data",
      "name": "name_data",
      "schemaId": "schemaId_data",
      "registryId": "registryId_data",
      "namingStrategy": "namingStrategy_data"
    }
  ]
} 

Answer 1

You can just do the whole thing in SQL using FOR JSON.

Unfortunately, arrays are not possible using this method. There are anumber of hacks, but the easiest one in your situation is to just append to [] using JSON_MODIFY

SELECT
  authorIds = JSON_MODIFY('[]', 'append $', a.authorIds),
  [aminoAcids],
  [name],
  [schemaId],
  [registryId],
  [namingStrategy]
FROM aaSequences a
FOR JSON PATH, ROOT('aaSequences');

db<>fiddle