json snippets

How to pretty print JSON data with Ruby

Tagged ruby, json, pretty print  Languages ruby

This ugly one-liner will pretty print the JSON in countries.json, so that it's easier to read and edit:

cat db/seeds/countries.json | ruby -rubygems -e 'require "json";a=JSON.pretty_generate JSON.parse gets;File.open("db/seeds/countries.json", "w"){|f| f << a}'

How to use Python's simplejson to read and write JSON data

Tagged simplejson, python, json  Languages python

First you need to install simplejson:

easy_install simplejson

Now you can dump data to JSON:

import simplejson as json

class Something:

    def __init__(self):
        self.test = "test"

    def to_json(self):
        return json.dumps(self.__dict__)

Or if you have complex objects:

import simplejson as json
class Something:

    def __init__(self):
        self.test = [Other('a', 'b'), Other('a', 'c')]

    def to_json(self):
        return json.dumps([p.__dict__ for p in self.devices])

How to create a JSONP cross-domain webservice with Sinatra and Ruby

Tagged json, jsonp, sinatra, jquery  Languages ruby

Your Sinatra app:

get '/' do
    callback = params.delete('callback') # jsonp
    json = {'your' => 'data'}.to_json

    if callback
      content_type :js
      response = "#{callback}(#{json})" 
      content_type :json
      response = json

Your HTML:

<script type="text/javascript">
    function parseResponse(json) {
    // Do something with the data
    <script type="text/javascript" src="http://xxx.com/?callback=parseResponse"></script>

You can also do the same with jQuery:

    type: 'get',
    url: '/',
    dataType: 'jsonp',
    success: function(data) {

How to export a Google Doc spreadsheet to CSV

Tagged csv, json, google, spreadsheet  Languages 

It's no longer possible to export Google Spreadsheet's to CSV, so use JSON instead:


You can find the SPREADSHEET_KEY in the Google Doc's original URL.

You can also use the Google Drive API.

Render Rabl Views Anywhere (views, jobs, mails)

Tagged rabl, json, render, helper  Languages ruby

A helper for rendering Rabl views anywhere, e.g. background jobs, views, mails, etc:

def render_rabl(locals, view, options = {})
  scope = Object.new
  locals.each do |key, value|
    scope.instance_variable_set :"@#{key}", value
  options = { format: :json, scope: scope }.merge(options)
  Rabl.render(nil, view, options)


locals = { users: User.all }
view = 'users/index'
render_rabl locals, view

How to Use the JSON and HStore Postgres Data Types With Rails 4

Tagged rails, json, hstore, postgres, nosql  Languages ruby

First enable the hstore Postgres extension. In this example we define both a JSON and an HStore column:

class Schema < ActiveRecord::Migration
  def change
    enable_extension "hstore"
    create_table :links do |t|
      t.hstore :data
      t.column :settings, :json

Next, we specify accessors for the data that we will be stored in the JSON and HStore columns:

class Link < ActiveRecord::Base
  # Use hstore for text-only data:
  store :data, :name, :url, :description
  # Use JSON to support string, number, object, array, true, false, null
  store :settings, :update_interval, :created_at, :updated_at

We can now use the defined ActiveRecord attributes to store and access JSON and HStore data:

Link.create! name: 'Google', url: 'http://google.com', description: 'Ad company', update_interval: 1.day, created_at: Time.now.utc

Querying the data is where you'll see the biggest differences. Two examples:

# hstore
Link.where("data -> 'name' = ?", 'Google')
# json
Link.where("CAST(settings->>'update_interval' as integer) = ?", 1.day.to_s)

Notes for Postgres 9.3: * HStore can store only text. Nested data is not supported. * JSON supports the following types: string, number, object, array, true, false, null. For example, date and time types are not supported. Nested data is supported.

Postgres SQL query for extracting and querying a JSON object containing an array of JSON objects

Tagged json, jsonb, postgres, sql  Languages json, sql

Postgres 12 supports the SQL/JSON Path Language which makes querying JSON documents a lot easier than before: https://www.postgresql.org/docs/12/functions-json.html#FUNCTIONS-SQLJSON-PATH



For older versions we have to do the extraction the hard way…

For example, if we want to extract data from a JSONB column stored in PostgreSQL. The JSON object (hash, dictionary, map, object) contains an array of JSON objects:

  "responses": [
      "patient": {
        "ssid": "101010-XXXX",
      "patient": {
        "ssid": "070710-XXXX",

In Postgresql 9.4 and higher we can write the following query to query nested arrays of objects:

   body -> 'responses' @> '[{"patient":[{"ssid":"070710-XXXX"}]}]';

In earlier versions of Postgres we can use the jsonb_array_elements function:

WITH json_messages AS (
 SELECT jsonb_array_elements(body#>'{responses}')->'patient'->>'ssid', id from messages
SELECT * FROM json_messages WHERE ssid = '010150-XXXX';

To extract the values, we can use a lateral join and jsonb_array_elements:

SELECT DISTINCT(o.data -> 'status')
FROM json_messages m, jsonb_array_elements(m.body_json -> 'reports') d(data), jsonb_array_elements(d.data -> 'results') o(data)


How to convert a HTML table to CSV or JSON

Tagged csv, html, table, json  Languages ruby
require 'nokogiri'
require 'net/http'
require 'csv'
require 'json'

url = 'http://site/html.table'
uri = URI(url)
response = Net::HTTP.get(uri)

doc = Nokogiri::HTML(response)
table = doc.xpath('//table//tr').map do |row|
  row.xpath('td').map do |cell|
puts JSON.pretty_generate(table)