Flatten a JSON object with other embedded JSON objects

Question

I have a JSON object which looks like this:

{
  "name":"bacon"
  "category":["food","meat","good"]
  "calories":"huge"
}

I am trying to flatten that into an array of unique values. I need to build a fact table for Tableau which is not able to work with cross-tabulated data or JSON data directly.

I am not picky about whether I do this in Python or Ruby, but so far I've been trying to do it in Ruby. I am able to easily parse the JSON and get a Ruby hash out of it which seems like the right thing to do first.

{"name"=>"bacon", "category"=>["food", "meat", "good"], "calories" => "huge"}

and I need to produce this:

name,category,calories
bacon,food,huge
bacon,meat,huge
bacon,good,huge

So I think I need to loop through that hash and try to un-nest it. I've been experimenting with something like this:

def Flatten(inHash)
    inHash.each do |key,value|
        if value.kind_of?(Hash)
            Flatten(value)
        else
            puts "#{value}"
        end 
    end 
end

But and that seems to print all of the values, but it doesn't repeat the value that came before. So I get output that looks like

bacon
food
meat
good
huge

Is there a built-in method or gem or library that will this or am I looking at building from scratch? Any ideas on how to get the output that I want? I speak Ruby and Python so if you've got a Python answer please share.

Answer 1

>>> #Assuming your json data is correctly formatted as is as follows
>>> data = '{ "name":"bacon", "category":["food","meat","good"], "calories":"huge" }'
>>> #Lets call our json parser as foo (I am bad with names)
>>> def foo(data):
    #You first need to parse it to a Py Object
    json_data = json.loads(data)
    from collections import namedtuple
    #Now create a namedtuple with the given keys of the dictionary
    food_matrix = namedtuple('food_matrix',json_data.keys())
    #And create a tuple out of the values
    data_tuple = food_matrix(*json_data.values())
    #Now with itertools.product create a cross product
    from itertools import product
    data_matrix = list(product([data_tuple.name],data_tuple.category, [data_tuple.calories]))
    # Now display the heading
    print "{:15}{:15}{:15}".format(["name","category","calories")
    # Now display the values
    for e in data_matrix:
        print "{:15}{:15}{:15}".format(*e)


>>> #Now call it
>>> foo(data)
name           category       calories                  
bacon          food           huge           
bacon          meat           huge           
bacon          good           huge           
>>> 

Answer 2

Assuming your JSON has commas (to make it valid JSON), you could use itertools.product to enumerate all possible combinations:

import itertools as IT
import json

text = '{ "name":"bacon", "category":["food","meat","good"], "calories":"huge" }'
data = json.loads(text)

# Sort the keys in the order they appear in `text`
keys = sorted(data.keys(), key = lambda k: text.index(k))

# Promote the values to lists if they are not already lists
values = [data[k] if isinstance(data[k], list) else [data[k]] for k in keys]

print(','.join(keys))
for row in IT.product(*values):
    print(','.join(row))

yields

name,category,calories
bacon,food,huge
bacon,meat,huge
bacon,good,huge

Answer 3

Here would be my solution:

require 'json'

# Given a json object
json = JSON.parse('{"name":"bacon", "category":["food","meat","good"], "calories":"huge"}')

# First, normalize all the values to arrays
hash = Hash[json.map{|k, v| [k, [v].flatten]}]

# We now have a hash like {"name" => ["bacon"], ...}

# Then we'll make the product of the first array of values 
# (in this case, ["bacon"]) with the other values
permutations = hash.values[0].product(*hash.values[1..-1])

# Now just need to output
puts hash.keys.join(",")
permutations.each{ |group| puts group.join(",") }