0

I am trying to create JSON in Ruby from data coming from a SQL Server table, based off a query. I've worked with Ruby quite a bit and JSON some. But never together.

This is a sample of the JSON I'm trying to create.

Even help with just creating the JSON with the nested arrays and the root element would be helpful.

{
  "aaSequences": [
    {
     "authorIds": [
        "ent_fdfdfdfdf_one"
      ],
      "aminoAcids": "aminoAcids_data",
      "name": "bbbbb-22",
      "schemaId": "ls_jgjgjg",
      "registryId": "src_fgfgfgf",
      "namingStrategy": "NEW_IDS"
    },
    {
     "authorIds": [
        "ent_fdfdfdfdf_two"
      ],
      "aminoAcids": "aminoAcids_data",
      "name": "bbbbb-22",
      "schemaId": "ls_jgjgjg",
      "registryId": "src_fgfgfgf",
      "namingStrategy": "NEW_IDS"
    }
  ]
}
Dale K
  • 25,246
  • 15
  • 42
  • 71
hollecar
  • 17
  • 4
  • Have you considered using the T-SQL JSON functions to return your result set already in JSON format? – Martin Cairney Mar 29 '22 at 02:41
  • Yes I’ve tried a lot with the sql functions. I was getting stuck on how to format it correctly and creating the nested arrays. – hollecar Mar 29 '22 at 10:57

1 Answers1

0

Generate a JSON from a Ruby hash object

To generate a json, first start with a hash (like a dict) in Ruby

my_hash = {:foo => 1, :bar => 2, :baz => 3}

Make sure you require the json package as well

require 'json'

Then you can simply convert the hash object to a JSON string

my_hash.to_json # outputs: "{'foo': 1, 'bar': 2, 'baz': 3'}"

You can nest arrays into your hash as well

my_hash_2 = {:foo => [1, 2, 3, 4], :bar => ['a', 'b', 'c', 'd']}

I'll let you try that one on your own, but ruby will handle the nested object just fine for you.

From the docs

kpaul
  • 355
  • 6
  • 16
  • Okay I think I have it... ` require 'json' my_hash_3 = {:aaSequences => [ { :authorIds => [ 'ent_fdfdfdfdf_one' ], :aminoAcids => 'aminoAcids_data', :name => 'bbbbb-22', :schemaId => 'ls_jgjgjg', :registryId => 'src_fgfgfgf', :namingStrategy => 'NEW_IDS' } ] } output2 = JSON.pretty_generate(my_hash_3) File.open("temp4.json","w") do |f| f.write(output2) end ` – hollecar Mar 29 '22 at 13:12
  • thank you, now I need to connect to SQL Server run a query and assign the hash values from the SQL Server fields in a for loop. Ive used tiny_tds before...is this the best gem now for this? Also what is the easiest way to do this? – hollecar Mar 29 '22 at 13:19
  • I ended up just using tinytds and throwing the values into a hash and then throwing those into the JSON hash...works great... Thank you for your help................................................ ` jsonFileOutput0 = {:aaSequences => [{:authorIds => ["#{authorIds}"],:aminoAcids => "#{aminoAcids}",:name => "#{name}",:schemaId => "#{schemaId}",:registryId => "#{registryId}",:namingStrategy => "#{namingStrategy}"}]} ` – hollecar Mar 29 '22 at 14:21
  • how would I add a comma between each record, in the JSON file? Do I need to append to the bottom of the file and insert the comma each time? – hollecar Mar 29 '22 at 15:33
  • I'm not exactly sure what your file layout is like, but if you have a list of all your jsons do this: `joined = all_jsons.join("\n,\n")`. Then write `all_jsons` to the file. – kpaul Mar 29 '22 at 23:29