使用RUBY回圈通過SQLServer表創建json

這是對這個問題的跟進：

Ruby 從 SQL Server 創建 JSON

我能夠在 JSON 中創建嵌套陣列。但我正在努力遍歷記錄并為每條記錄附加一個檔案。另外，我將如何在 json 的頂部而不是在每條記錄上添加根元素。“aaSequences” 只需要在頂部出現一次......我還需要在每條記錄之間使用逗號。

到目前為止，這是我的代碼

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

這是到目前為止它創建的 json 示例...

{
  "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"
    }
  ]
}

這是我需要它看起來像的一個例子

{
  "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"
    }
  ]
} 

uj5u.com熱心網友回復：

您可以在 SQL 中使用FOR JSON.

不幸的是，使用此方法無法創建陣列。有很多技巧，但在您的情況下，最簡單的方法是附加到[]usingJSON_MODIFY

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

db<>小提琴

標籤：json sql服务器 红宝石

上一篇：使用EXCEPT獲取第二條記錄

下一篇：SQLServer：排除名稱列與第二個排除表中的值匹配的記錄