5 ms·

It isn't easy (perhaps not even possible) to get the name of the fruit Bob grows in his farm using any of these tools and the following data: { "models":

by fiatjaf 11y ago

It isn't easy (perhaps not even possible) to get the name of the fruit Bob grows in his farm using any of these tools and the following data: { "models": [{ "title": "fruits", "fields": [ {"name": "Name", "key": "3746"}, {"name": "Colour", "key": "4867"} ], "entities": [{ "_id": "372612", "3746": "Orange", "4867": "orange" }] }, { "title": "farmers", "fields": [ {"name": "Full name", "key": "8367"}, {"name": "Address", "key": "3947"}, {"name": "Fruits", "key": "5243"} ], "entities": [{ "_id": "747463", "8367": "Bob, the farmer", "3947": "Farmland", "5243": ["372612"] }] }] }

sdegutis 11y ago
That's true even in Clojure, arguably the simplest and cleanest language ever invented for complex data transformation and extraction. The Clojure solution to this still ends up requiring temporary variables and some sort of model transformation functionality. (Will try to post my Clojure solution in 5 hours after my next noprocrast timer is up.) If the data could first be transformed so that it doesn't require temporary variables or ad-hoc transformation function definitions, instead making use of "paths", then it would be easier with command line tools. Such a transformation could be possible as its own command line interface.
- ktRolster 11y ago
  That's true even in Clojure, arguably the simplest and cleanest language ever invented for complex data transformation and extraction. Interesting, I didn't realize Clojure had a reputation for data transformation and extraction
- tekacs 11y ago
  Yup, lots of temporaries (I haven't written much Clojure recently, so I'm sure I'm missing lots of simplifying core fns). (let [; input js (cheshire.core/parse-string (slurp (clojure.java.io/resource "awful.json"))) ; utils get-match (fn [k v coll] (first (filter #(= (get % k) v) coll))) model-for (fn [title toplevel] (get-match "title" title (toplevel "models"))) key-for (fn [name* model] ((get-match "name" name* (model "fields")) "key")) ; models fruit-model (model-for "fruits" js) farmer-model (model-for "farmers" js) ; keys fruit-name-key (key-for "Name" fruit-model) farmer-name-key (key-for "Full name" farmer-model) farmer-fruits-key (key-for "Fruits" farmer-model) ; values bob-entity (get-match farmer-name-key "Bob, the farmer" (farmer-model "entities")) bob-fruit-keys (bob-entity farmer-fruits-key) bob-fruit-entities (map #(get-match "_id" % (fruit-model "entities")) bob-fruit-keys) bob-fruit-names (map #(% fruit-name-key) bob-fruit-entities)] bob-fruit-names) => ("Orange")
- sdegutis 11y ago
  Got this far and gave up: (let [models (->> (get (clojure.data.json/read-str json-string) "models") (clojure.walk/keywordize-keys)) fields (->> models (mapcat :fields) (map (juxt (comp keyword :key) :name)) (into {})) entities (->> models (mapcat :entities) (map (juxt :_id (fn [entity] (->> (for [[k v] (dissoc entity :_id)] [(get fields k) v]) (into {}))))) (into {})) ] ) Posted for posterity.
deleted 11y ago
[deleted]
jlarocco 11y ago
First of all, I have little sympathy for people who create JSON like that. You created a messy, hard to use JSON "schema", it should be little surprise to anybody that it's messy and hard to use. FWIW though, jq can do the query, but I'm not going to spend the time doing it.
- djKianoosh 11y ago
  I'm with you. Why would anyone intentionally create data like that?
- fiatjaf 11y ago
  I've seen JSON like this many times, and I also hate it, but I'm hardly going to use jq to fiddle with JSON I've written myself.
  - jlarocco 11y ago
    I don't really see what your complaint is. The problem you're describing isn't a shortcoming of jq and the other tools in the article, it's a shortcoming of the brain dead JSON encoding. If you need to process data like that, you'll have to console yourself to the fact it won't be process-able as a shell one-liner.
timmclean 11y ago
I've been meaning to learn jq, so I decided to give it a try. FRUITS=$(cat input.json | jq '.models | map(select(.title == "fruits")) | .[0]') FRUIT_NAME_KEY=$(echo "$FRUITS" | jq '.fields | map(select(.name == "Name")) | .[0].key') FARMERS=$(cat input.json | jq '.models | map(select(.title == "farmers")) | .[0]') FARMER_NAME_KEY=$(echo "$FARMERS" | jq '.fields | map(select(.name == "Full name")) | .[0].key') FARMER_FRUITS_KEY=$(echo "$FARMERS" | jq '.fields | map(select(.name == "Fruits")) | .[0].key') BOB=$(echo "$FARMERS" | jq '.entities | map(select(.['$FARMER_NAME_KEY'] == "Bob, the farmer")) | .[0]') BOB_FRUIT_IDS=$(echo "$BOB" | jq '.['$FARMER_FRUITS_KEY'] | .[]' -r) for BOB_FRUIT_ID in "$BOB_FRUIT_IDS"; do echo "$FRUITS" | jq '.entities | map(select(._id == "'$BOB_FRUIT_ID'")) | .[0] | .['$FRUIT_NAME_KEY']' done There's a bit of bash boilerplate, but honestly it was about what I would expect, given a structure with so many layers of indirection. Pain points: * Switching between bash and jq's filtering language led me to use string interpolation with bash variables. Malicious inputs can probably exploit this (and it was just awkward anyway). * A "select one" filter would be nice, instead of select + get first element.
- andolanra 11y ago
  jq is powerful enough to express it in one query: it has variables (using expr as name) which make this thing at least vaguely feasible. That doesn't mean you should, but you could: .models | (.[] | if .title == "farmers" then (.fields | .[] | if .name == "Fruits" then .key else empty end) as $fruits | (.fields | .[] | if .name == "Full name" then .key else empty end) as $name | .entities | .[] | if .[ $name ] == "Bob, the farmer" then .[ $fruits ] else empty end else empty end) as $fruits | .[] | if .["title"] == "fruits" then ( .fields | .[] | if .name == "Name" then .key else empty end) as $fruit_name | .entities | [ .[] | {(._id): .[$fruit_name]} ] | add as $lookup | $fruits | .[] | $lookup[.] else empty end (I'm not claiming this is the best way to write that query, but it's the first one I came up with.)
- davvid 11y ago
  Here's the equivalent using jsonaxe[1]. The main difference is the python-like syntax, which is either good or bad depending on your tastes. The pain point about string interpolation remains, tho. data=input.json fruits () { jsonaxe 'models.filter(lambda x: x["title"] == "fruits")[0]' "$data" } farmers () { jsonaxe 'models.filter(lambda x: x["title"] == "farmers")[0]' "$data" } fruit_name_key=$(fruits | jsonaxe --raw 'fields.filter(lambda x: x["name"] == "Name")[0].key') farmer_name_key=$(farmers | jsonaxe --raw 'fields.filter(lambda x: x["name"] == "Full name")[0].key') farmer_fruits_key=$(farmers | jsonaxe --raw 'fields.filter(lambda x: x["name"] == "Fruits")[0].key') farmers | # get bob jsonaxe "entities.filter(lambda x: x['$farmer_name_key'] == 'Bob, the farmer')[0]" | # get bob's fruits key jsonaxe --raw "get('$farmer_fruits_key')" | while read fruit_id do fruits | jsonaxe "entities.filter(lambda x: x['_id'] == '$fruit_id')[0]" | jsonaxe "get('$fruit_name_key')" done [1] https://github.com/davvid/jsonaxe https://github.com/davvid/jsonaxe
junke 11y ago
For details, see http://pastebin.com/raw/1W88ywuu http://pastebin.com/raw/1W88ywuu The actual test: (defparameter *model* (make-model (cl-json:decode-json-from-source #P"/tmp/json.test"))) (with-table (farmers :title "farmers" :model *model* :accessors ((farmer-name "Full name") (farmer-fruits "Fruits"))) (dolist (farmer (table-entities farmers)) (when (ppcre:scan "Bob" (farmer-name farmer)) (with-table (fruits :title "fruits" :model *model* :accessors ((fruit-name "Name")) :index t) (return (loop for fid in (farmer-fruits farmer) collect (fruit-name (iget fruits fid)))))))) The :accessors arguments is used to resolve field access once for all when visiting a table. If :index is true, we build a temporary hash-table based on identifiers.

by fiatjaf 11y ago

sdegutis 11y ago

That's true even in Clojure, arguably the simplest and cleanest language ever invented for complex data transformation and extraction. The Clojure solution to this still ends up requiring temporary variables and some sort of model transformation functionality. (Will try to post my Clojure solution in 5 hours after my next noprocrast timer is up.) If the data could first be transformed so that it doesn't require temporary variables or ad-hoc transformation function definitions, instead making use of "paths", then it would be easier with command line tools. Such a transformation could be possible as its own command line interface.

ktRolster 11y ago
That's true even in Clojure, arguably the simplest and cleanest language ever invented for complex data transformation and extraction. Interesting, I didn't realize Clojure had a reputation for data transformation and extraction
tekacs 11y ago
Yup, lots of temporaries (I haven't written much Clojure recently, so I'm sure I'm missing lots of simplifying core fns). (let [; input js (cheshire.core/parse-string (slurp (clojure.java.io/resource "awful.json"))) ; utils get-match (fn [k v coll] (first (filter #(= (get % k) v) coll))) model-for (fn [title toplevel] (get-match "title" title (toplevel "models"))) key-for (fn [name* model] ((get-match "name" name* (model "fields")) "key")) ; models fruit-model (model-for "fruits" js) farmer-model (model-for "farmers" js) ; keys fruit-name-key (key-for "Name" fruit-model) farmer-name-key (key-for "Full name" farmer-model) farmer-fruits-key (key-for "Fruits" farmer-model) ; values bob-entity (get-match farmer-name-key "Bob, the farmer" (farmer-model "entities")) bob-fruit-keys (bob-entity farmer-fruits-key) bob-fruit-entities (map #(get-match "_id" % (fruit-model "entities")) bob-fruit-keys) bob-fruit-names (map #(% fruit-name-key) bob-fruit-entities)] bob-fruit-names) => ("Orange")
sdegutis 11y ago
Got this far and gave up: (let [models (->> (get (clojure.data.json/read-str json-string) "models") (clojure.walk/keywordize-keys)) fields (->> models (mapcat :fields) (map (juxt (comp keyword :key) :name)) (into {})) entities (->> models (mapcat :entities) (map (juxt :_id (fn [entity] (->> (for [[k v] (dissoc entity :_id)] [(get fields k) v]) (into {}))))) (into {})) ] ) Posted for posterity.

deleted 11y ago

[deleted]

jlarocco 11y ago

First of all, I have little sympathy for people who create JSON like that. You created a messy, hard to use JSON "schema", it should be little surprise to anybody that it's messy and hard to use. FWIW though, jq can do the query, but I'm not going to spend the time doing it.

djKianoosh 11y ago
I'm with you. Why would anyone intentionally create data like that?
fiatjaf 11y ago
I've seen JSON like this many times, and I also hate it, but I'm hardly going to use jq to fiddle with JSON I've written myself.
- jlarocco 11y ago
  I don't really see what your complaint is. The problem you're describing isn't a shortcoming of jq and the other tools in the article, it's a shortcoming of the brain dead JSON encoding. If you need to process data like that, you'll have to console yourself to the fact it won't be process-able as a shell one-liner.

timmclean 11y ago

I've been meaning to learn jq, so I decided to give it a try. FRUITS=$(cat input.json | jq '.models | map(select(.title == "fruits")) | .[0]') FRUIT_NAME_KEY=$(echo "$FRUITS" | jq '.fields | map(select(.name == "Name")) | .[0].key') FARMERS=$(cat input.json | jq '.models | map(select(.title == "farmers")) | .[0]') FARMER_NAME_KEY=$(echo "$FARMERS" | jq '.fields | map(select(.name == "Full name")) | .[0].key') FARMER_FRUITS_KEY=$(echo "$FARMERS" | jq '.fields | map(select(.name == "Fruits")) | .[0].key') BOB=$(echo "$FARMERS" | jq '.entities | map(select(.['$FARMER_NAME_KEY'] == "Bob, the farmer")) | .[0]') BOB_FRUIT_IDS=$(echo "$BOB" | jq '.['$FARMER_FRUITS_KEY'] | .[]' -r) for BOB_FRUIT_ID in "$BOB_FRUIT_IDS"; do echo "$FRUITS" | jq '.entities | map(select(._id == "'$BOB_FRUIT_ID'")) | .[0] | .['$FRUIT_NAME_KEY']' done There's a bit of bash boilerplate, but honestly it was about what I would expect, given a structure with so many layers of indirection. Pain points: * Switching between bash and jq's filtering language led me to use string interpolation with bash variables. Malicious inputs can probably exploit this (and it was just awkward anyway). * A "select one" filter would be nice, instead of select + get first element.

andolanra 11y ago
jq is powerful enough to express it in one query: it has variables (using expr as name) which make this thing at least vaguely feasible. That doesn't mean you should, but you could: .models | (.[] | if .title == "farmers" then (.fields | .[] | if .name == "Fruits" then .key else empty end) as $fruits | (.fields | .[] | if .name == "Full name" then .key else empty end) as $name | .entities | .[] | if .[ $name ] == "Bob, the farmer" then .[ $fruits ] else empty end else empty end) as $fruits | .[] | if .["title"] == "fruits" then ( .fields | .[] | if .name == "Name" then .key else empty end) as $fruit_name | .entities | [ .[] | {(._id): .[$fruit_name]} ] | add as $lookup | $fruits | .[] | $lookup[.] else empty end (I'm not claiming this is the best way to write that query, but it's the first one I came up with.)
davvid 11y ago
Here's the equivalent using jsonaxe[1]. The main difference is the python-like syntax, which is either good or bad depending on your tastes. The pain point about string interpolation remains, tho. data=input.json fruits () { jsonaxe 'models.filter(lambda x: x["title"] == "fruits")[0]' "$data" } farmers () { jsonaxe 'models.filter(lambda x: x["title"] == "farmers")[0]' "$data" } fruit_name_key=$(fruits | jsonaxe --raw 'fields.filter(lambda x: x["name"] == "Name")[0].key') farmer_name_key=$(farmers | jsonaxe --raw 'fields.filter(lambda x: x["name"] == "Full name")[0].key') farmer_fruits_key=$(farmers | jsonaxe --raw 'fields.filter(lambda x: x["name"] == "Fruits")[0].key') farmers | # get bob jsonaxe "entities.filter(lambda x: x['$farmer_name_key'] == 'Bob, the farmer')[0]" | # get bob's fruits key jsonaxe --raw "get('$farmer_fruits_key')" | while read fruit_id do fruits | jsonaxe "entities.filter(lambda x: x['_id'] == '$fruit_id')[0]" | jsonaxe "get('$fruit_name_key')" done [1] https://github.com/davvid/jsonaxe https://github.com/davvid/jsonaxe

junke 11y ago

For details, see http://pastebin.com/raw/1W88ywuu http://pastebin.com/raw/1W88ywuu The actual test: (defparameter *model* (make-model (cl-json:decode-json-from-source #P"/tmp/json.test"))) (with-table (farmers :title "farmers" :model *model* :accessors ((farmer-name "Full name") (farmer-fruits "Fruits"))) (dolist (farmer (table-entities farmers)) (when (ppcre:scan "Bob" (farmer-name farmer)) (with-table (fruits :title "fruits" :model *model* :accessors ((fruit-name "Name")) :index t) (return (loop for fid in (farmer-fruits farmer) collect (fruit-name (iget fruits fid)))))))) The :accessors arguments is used to resolve field access once for all when visiting a table. If :index is true, we build a temporary hash-table based on identifiers.