how to read individual data from the content of remote JSON file in Ruby on Rails?

Question

I am trying to read individual data from the content of json API on Oil and Gas Authority website; however the code I have returns all the data. Any help would be highly appreciated.

require 'json'
require 'open-uri'

def index
  url='http://data-ogauthority.opendata.arcgis.com/datasets/ab4f6b9519794522aa6ffa6c31617bf8_0.geojson'
  @result = JSON.parse open(url).read
end

This my index view:

<% @result.each do |row| %>
  <%= row %> 
<% end %>

Is your problem (a) that your `@result` variable has more data than you want (i.e., you want to get more targeted data back from the API call) or (b) that you are having trouble extracting the data you want from your `@result` variable? — jvillian, Jun 27 '17 at 15:29
Your question, then, isn't a `ruby-on-rails` question (nor a `json` or `ruby` question). It's an ArcGIS question. — jvillian, Jun 27 '17 at 15:59
Data on field name, field type, status, discovery data and discovery well. — fred, Jun 27 '17 at 16:09
Check [this](https://itportal.decc.gov.uk/arcgis/rest/services/OGA_Public_WGS84/OGA_Offshore_Fields_WGS84/MapServer/0/query) out, it might help filter from source. — Gerry, Jun 27 '17 at 16:39
Also instead of default [ruby JSON module](http://ruby-doc.org/stdlib-2.4.1/libdoc/json/rdoc/JSON.html) you can use [Oj gem](https://github.com/ohler55/oj). Its much faster and more preferable choice if you have lots of json decoding/encoding. — Martin, Jun 27 '17 at 20:26

jvillian · Answer 1 · 2017-06-27T16:20:23.013

Given that the API (as you are currently using it) returns a JSON structure like this:

{
  "type":"FeatureCollection",
  "features":[
    {
      "type":"Feature",
      "properties":{
        "FIELDNAME":"GRYPHON",
        "FIELDTYPE":"OIL",
        "NAME_SHORT":"GRYPHON",
        "STATUS":"PRODUCING",
        "DISC_DATE":"1987/07",
        "DISC_WELL":"9/18b-7",
        "STAT_CODE":"800",
        "PROD_DATE":"1993/10",
        "DEPTH_M":"111.86",
        "DET_STATUS":"DETERMINED",
        "ORIG_OP":"KERR-MCGEE",
        "ORIG_LIC":"P.496",
        "ORIG_LIC_2":"P.478",
        "ORIG_LIC_3":"P.257",
        "ORIG_LIC_4":"P.103",
        "ORIG_LIC_5":" ",
        "CURR_OPER":"MAERSK OIL NORTH SEA UK LIMITED",
        "FIELDDATA":"https://itportal.decc.gov.uk/fields/fields_index/ukcs+field+information+listed+by+field+name/183.htm",
        "OBJECTID":16,
        "OGA_COP":null
      },
      "geometry":{
        "type":"Polygon",
        "coordinates":[
          [
            [1.5701447246411744,59.35253688325039],
            ...
          ]
        ]
      }
    },
    ...
  ]
}

You could do something like:

<% @result[:features].each do |feature| %>
  <%= feature[:properties][:FIELDNAME] %>
  <%= feature[:properties][:FIELDTYPE] %>
  ...
<% end %>

Your JSON file looks to be something like 1.3MB. So, unless you can figure out how to filter your results on the API side (using query params, I would suppose), you may end up with various performance issues in retrieving the JSON.

And, you may want to do:

@result = JSON.parse(open(url).read).with_indifferent_access

So that you can use symbols to access hash elements as well as strings.

Thanks Guys everything works fine but am also trying to keep the data in my database how should go about that. — fred, Jun 29 '17 at 11:02
@fred if you are using posgres > 9.3, there is a `JSON` column type. Otherwise you can use `text` and use Rails `serialize` method. Take a look at this article from CodeShip: https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/ — dimitry_n, Jul 10 '17 at 16:31

score 1 · Answer 2 · answered Jun 27 '17 at 16:25

1

One thing to add to @jvillian answer is that if one of the keys is nil then calling this branch's subsequent keys will result in undefined method '[]'. Ruby 2.3+ has a new method called dig which will simply return nil. More on dig in my answer to this question.

answered Jun 27 '17 at 16:25

dimitry_n

2,939
1
30
53

1

Great addition. Thank you! – jvillian Jun 27 '17 at 16:32

Gerry · Answer 3 · 2017-06-27T17:04:04.047

Also to add to @jvillian answer, you can fetch the filtered information using this link; for example, taking into account the fields you need:

FIELDNAME
FIELDTYPE
STATUS
DISC_DATE
DISC_WELL

You could create query that will result in the following response:

{
 "displayFieldName": "FIELDNAME",
 "fieldAliases": {
  "FIELDNAME": "Field Name",
  "FIELDTYPE": "Field Type",
  "STATUS": "Status",
  "DISC_DATE": "Discovery Date",
  "DISC_WELL": "Discovery Well"
 },
 "fields": [
  {
   "name": "FIELDNAME",
   "type": "esriFieldTypeString",
   "alias": "Field Name",
   "length": 32
  },
  {
   "name": "FIELDTYPE",
   "type": "esriFieldTypeString",
   "alias": "Field Type",
   "length": 4
  },
  {
   "name": "STATUS",
   "type": "esriFieldTypeString",
   "alias": "Status",
   "length": 50
  },
  {
   "name": "DISC_DATE",
   "type": "esriFieldTypeString",
   "alias": "Discovery Date",
   "length": 20
  },
  {
   "name": "DISC_WELL",
   "type": "esriFieldTypeString",
   "alias": "Discovery Well",
   "length": 20
  }
 ],
 "features": [
  {
   "attributes": {
    "FIELDNAME": "GRYPHON",
    "FIELDTYPE": "OIL",
    "STATUS": "PRODUCING",
    "DISC_DATE": "1987/07",
    "DISC_WELL": "9/18b-7"
   }
  },
  {
   "attributes": {
    "FIELDNAME": "BRAEMAR",
    "FIELDTYPE": "COND",
    "STATUS": "PRODUCING",
    "DISC_DATE": "1995/05",
    "DISC_WELL": "16/03b-8Z"
   }
  },
  ...
 ]
}

This file will be now 76KB, and you can extract the data in almost the same way, just change properties for attributes, i.e.:

<% @result[:features].each do |feature| %>
  <%= feature[:attributes][:FIELDNAME] %>
  <%= feature[:attributes][:FIELDTYPE] %>
  ...
<% end %>

Thanks Guys everything works fine now but am also trying to keep the data in my database how should go about that. — fred, Jun 29 '17 at 11:02
@fred Glad to help! Please don't forget to accept the answer that worked for you (by clicking on the tick) and, if other answers also helped, you can upvote them (by clicking the up arrow). :) — Gerry, Jun 29 '17 at 12:12
@fred About your question, in general, you could create a model and iterate through the data you receive creating a record each time. I will suggest you to post a new question, that way you will get more detalied answers and more people will see it. — Gerry, Jun 29 '17 at 12:16

how to read individual data from the content of remote JSON file in Ruby on Rails?

3 Answers3