[timmclean]

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]

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]

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