Ruby create JSON from SQL Server

Question

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

Have you considered using the T-SQL JSON functions to return your result set already in JSON format? — Martin Cairney
– Martin Cairney, Commented Mar 29, 2022 at 2: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
– hollecar, Commented Mar 29, 2022 at 10:57

kpaul · Accepted Answer · 2022-03-28 22:22:43Z

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

answered Mar 28, 2022 at 22:22

kpaul

3856 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

hollecar Over a year ago

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 Over a year ago

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 Over a year ago

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 Over a year ago

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?

kpaul Over a year ago

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.

Jeremy Caney · Accepted Answer · 2024-08-16 21:33:22Z

0

To generate 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.

edited Aug 16, 2024 at 21:33

Jeremy Caney

7,808115 gold badges58 silver badges86 bronze badges

answered Aug 12, 2024 at 11:41

AJP

1

Collectives™ on Stack Overflow

Ruby create JSON from SQL Server

2 Answers 2

Generate a JSON from a Ruby hash object

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Generate a JSON from a Ruby hash object

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related