Question on how to input value of a node A, and get sibling node B's value back

[RickCogley]

Hello - I just noticed that there are a few general questions on here, under the query tag. I've read the documents but, I guess I'm floundering around. I hope you'll indulge a usage question.

Given sibling nodes A and B in json, where A and B are unique to the json stream I am looking at, and never repeated, my goal is to supply A and have B returned. Kind of a "lookup" concept.

I will access my json streams via a REST interface, using curl in a shell script. The json looks something like this:

{
  "id": "99999",
  "name": "My Database",
  "description": "Lorem ipsum, the description.",
  "culture": "en-US",
  "timeZone": "CST",
  "tables": [
    {
      "id": 12341,
      "recordName": "Company",
      "recordsName": "Companies",
      "alias": "t_12341",
      "showTab": true,
      "color": "#660000"
    },
    {
      "id": 12342,
      "recordName": "Order",
      "recordsName": "Orders",
      "alias": "t_12342",
      "showTab": true,
      "color": "#006600"
    },
    {
      "id": 12343,
      "recordName": "Order Item",
      "recordsName": "Order Items",
      "alias": "t_12343",
      "showTab": true,
      "color": "#000099"
    }
  ]
}

Actually, the pure json stream has no newlines, so passing it through '.' fixes that.

I can get a list of table aliases, using this command against a file I got via curl:

cat mytables.json | jq -r '{tablealias: .tables[].alias} | .tablealias' > tablealiases.txt

The tablealiases.txt file then looks like this, with one table alias per line:

t_12341
t_12342
t_12343

Then I can perform some action on the REST interface using that file as input, using a while loop like:

while read t; do curl -k "https://mydb.tld/api/{AUTHTOKEN}/$t/getinfo.json" | jq '.' > Table-$t-getinfo.json; done < tablealiases.txt

But what I'd like is, the result json Table-$t-getinfo.json to be named using two items from the original json. Instead of Table-t_12341-getinfo.json, I'd like to have Table-t_12341-Company-getinfo.json, for readability.

When I do my while/do/done loop, I already have variable $t containing the table alias, so I'd like to apply the contents of that variable to the json, to get the sibling of the table alias, the table name.

I have been using jqplay.org to try to do things like this:

"t_12341" as $tableindex | ({table: .tables[].recordName, alias: .tables[]} | .table)

... but it just returns the table names multiple times, like:

"Company"
"Company"
"Company"
"Order"
"Order"
"Order"
"Order Item"
"Order Item"
"Order Item"

Other variants like trying to use the variable in the filter content to pinpoint the record, like:

"t_12341" as $tableindex | ({table: .tables[].recordName, alias: .tables[].$tableindex} | .table)

... are not working at all, yielding an error like:

error: syntax error, unexpected '$', expecting FORMAT or QQSTRING_START
"t_12341" as $tableindex | ({table: .tables[].recordName, alias: .tables[].$tableindex} | .table) 1 compile error

I hope someone could kindly point me in the right direction.
Thanking you in advance.

Sincerely,
Rick

[pkoppstein]

@RickCogley - There are of course many ways to skin a cat, but if I understand what you're asking for correctly, then I believe the following hints should suffice:

Step 1:

jq -r '.tables[] | "\(.alias) \(.recordName)"' mytables.json > tablealiases.txt

Step 2:

while read t alias
do
    curl -k "https://mydb.tld/api/{AUTHTOKEN}/$t/getinfo.json" | jq '.' > "Table-$t-$alias-getinfo.json"
done < tablealiases.txt

[RickCogley]

@pkoppstein, thank you very much! That works like a charm! (I did not know you can tokenize an input stream to while read, nor the trick of isolating elements this way: "\(.alias) \(.recordName)". Nice!

[wtlangford]

While @pkoppstein answered your question, I'd like to shed some light on what's actually happening in "t_12341" as $tableindex | ({table: .tables[].recordName, alias: .tables[]} | .table), as I think this is something we need to work on explaining more clearly/prominently. It seems to be something many newcomers trip up on.

(Note that "object" below means a JSON object (a key-value map). "value" is used to mean any valid JSON type (number, string, object, array, null))

"t_12341" as $tableindex | ({table: .tables[].recordName, alias: .tables[]} | .table) is a tricky beast if you aren't careful. The [] operator causes jq to emit a stream of the values in that array, one at a time. Internally (on a high level), this is handled by saving this state of execution (internally, we fork the stack), and then for each value to be emitted, continuing execution from that point with that value. When there is no more work to be done, we backtrack to that saved state, extract the next value and move forwards again. Since you have two [] operators, for each value in the stream emitted by the first [], the second [] emits its full stream, resulting in a cartesian product. (Think for x in a { for y in b { stuff } }). Each result of this cartesian product will show up in jq's output. However, since you were only taking the .recordName field, you simply saw them showing up multiple times.

Admittedly, this is not my best explanation of what's happening here. The basic concept is that when [] (or any other generator) shows up multiple times, cartesian products start happening and this is often not what you meant/wanted.

Any further questions, just ask and we'll be more than happy to help!

[RickCogley]

@wtlangford, thanks! Yes, it felt like a cartesian product of two data sets, that you sometimes see in SQL syntax. I appreciate the extra explanation. Once I get the scripts working, I will write a little Cookbook entry for the wiki, referencing this Issue.

I do have another question which I intend to articulate in detail, but the gist is I want to grab two json streams, one a list of people and some basic attributes about them, and two a list of working days in this month. This time I do want to do a cartesian product of the two, resulting in one big stream with an entry per every date for every person. I may need help with it.

I appreciate the quick assistance, sincerely.

[pkoppstein]

@RickCogley - You're welcome. I thought a solution-oriented response would be both simpler and more helpful, but your approach ("to apply the contents of that variable to the json, to get the sibling of the table alias, the table name") can of course be made to work, too. In essence it would be based on this:

$ jq --arg t t_12341 '.tables[] | select(.alias == $t) | .recordName' mytables.json

"Company"

Regarding your two JSON streams -- version 1.4 of jq isn't very flexible when it comes to handling more than one JSON stream at a time, but you may find that the -s ("slurp") option will be part of the solution.

[RickCogley]

Thanks again, @pkoppstein. I'll try that too, as well as look into slurp when I start trying to accomplish the other solution.

[RickCogley]

FYI I added a recipe to the Cookbook on the wiki.

[nicowilliams]

Thanks!