Search code examples
javascriptphpjsonsocratasoda

Socrata consumerfinance.gov complaintdatabase API JSON parsing

I want to retrieve consumerfinance.gov complaintdatabase data using the Socrata API.

This is straightforward to retrieve the data using the instructions on the site at http://www.consumerfinance.gov/complaintdatabase/technical-documentation/#api-documentation

I used the following query with '6yuf-367p' to get just 'prepaid card' product data and the '.json' tag to get it in JSON format:

http://data.consumerfinance.gov/api/views/6yuf-367p/rows.json

I used PHP to retrieve data with this query:

$url = "http://data.consumerfinance.gov/api/views/6yuf-367p/rows.json";
$json = file_get_contents($url);
$data = json_decode($json);
var_dump($data);

The results are paraphrased below. A couple of things about the results that are different than what I was expecting.

  1. I wasn't expecting the 'Meta' section. The column names are in the Meta section not associated directly with the data in { key: value } format.

  2. I was expecting the Data section to have { key: value } format instead of just 'values'. This is different than the format described on Socrata help page at http://dev.socrata.com/docs/formats/json.html

I am not advanced javascript developer so I am wondering how best to proceed. Ideally I want only the 'Data' section with column names in the { key: value } format. I wanted it in that { key: value } format to use with things like Google Charts.

I am imaging I would have to save the column names in array and then loop through each data row and rewrite the data rows with column names included to get { key: value } format.

My other option would be to use the csv API format which is super clean with nice clean columns and no Meta section. But then I would have to convert the csv to JSON which seems unnecessary as the JSON is available.

CSV query is this:

http://data.consumerfinance.gov/api/views/6yuf-367p/rows.csv

So couple of questions:

  1. Does socrata provide an API feed without the 'Meta' section? Is there a filter I can use to exclude the 'Meta' section?

  2. If answer to #1 is no, does Socrata have ready javascript to parse the JSON to get the 'Data' section in { key: value } format similar format as described on Socrata help page?

Thanks!

    {
    "meta" : {
    "view" : {
    "id" : "6yuf-367p",
    "name" : "Prepaid Card Complaints",
    "averageRating" : 0,
    "createdAt" : 1434039311,

     etc etc

    "columns" : [ {
    "id" : -1,
    "name" : "sid",
    "dataTypeName" : "meta_data",
    "fieldName" : ":sid",
    "position" : 0,
    "renderTypeName" : "meta_data",
    "format" : {
    }
    }, {

    etc etc

    "data" : [ [ 208134, "A7A3941C-A764-44CA-ABC0-66DE814F1969", 208134, 1438091214, "924763", 1438091214, "924763", null, "2015-07-13T00:00:00", "Prepaid card", "General purpose card", "Managing, opening, or closing account", null, null, null, "Amex", "WA", "982XX", "Web", "2015-07-19T00:00:00", "Closed with monetary relief", "Yes", null, "1464043" ]

    ......

    ]
    }
Solution

  • It looks like you grabbed the wrong JSON URL. The one you grabbed is for the JSON export, which will dump you the entire dataset in a JSON format along with all of the metadata, and it doesn't provide a queryable API endpoint.

    Instead, you should use the endpoint https://data.consumerfinance.gov/resource/jhzv-w97w.json. That'll get you the {"key" : "value"} pairs you're looking for, like this:

    [ {
  "zip_code" : "982XX",
  "complaint_id" : "1464043",
  "issue" : "Managing, opening, or closing account",
  "date_received" : "2015-07-13T00:00:00",
  "state" : "WA",
  "product" : "Prepaid card",
  "company_response" : "Closed with monetary relief",
  "company" : "Amex",
  "submitted_via" : "Web",
  "date_sent_to_company" : "2015-07-19T00:00:00",
  "sub_product" : "General purpose card",
  "timely" : "Yes"
}, ... ]

    You can also view API docs for that dataset at: http://dev.socrata.com/foundry/#/data.consumerfinance.gov/jhzv-w97w

    Good luck, and let me know if you have more questions.