INNER JOIN in jq-CodePudding

Using the input given, I need to join the true "user" value from the tweets array with the id in the users array and display the users array object as part of the tweets array

Input:

{
  "tweets": [
    {
      "tweet": "Hey, i gonna release GPT4 soon",
      "user": 1
    },
    {
      "tweet": "We have launched falcon 10 yesterday, it was awesome, one step closer to Mars",
      "user": 2
    },
    {
      "tweet": "Databar acquires Statista.com, great news coming out",
      "user": 3
    },
    {
      "tweet": "Gpt4 is available",
      "user": 1
    }
  ],
  "users": [
    { "id": 1, "name": "a" },
    { "id": 2, "name": "b" },
    { "id": 3, "name": "c" }
  ]
}

Output

[
  {
    "tweet": "Hey, i gonna release GPT4 soon",
    "user": {
      "id": 1,
      "name": "a"
    }
  },
  {
    "tweet": "We have launched falcon 10 yesterday, it was awesome, one step closer to Mars",
    "user": {
      "id": 2,
      "name": "b"
    }
  },
  {
    "tweet": "Databar acquires Statista.com, great news coming out",
    "user": {
      "id": 3,
      "name": "c"
    }
  },
  {
    "tweet": "Gpt4 is available",
    "user": {
      "id": 1,
      "name": "a"
    }
  }
]

I tried using an if condition to find the equal values first, but it loops through the entire array, hence I cant get the specific value of the value in the users array that the ID is equal too

CodePudding user response：

Build an INDEX, then use that to map your tweets:

INDEX(.users[]; .id) as $idx | .tweets | map({ tweet, user: $idx[.user|tostring] })

or using JOIN directly:

[JOIN(INDEX(.users[]; .id); .tweets[]; .user|tostring; .[0]   { user: .[1] })]

You could also do it the inefficient way, finding the correct user by iterating:

.users as $users
| .tweets
| map({ tweet, user: (.user as $user | $users[] | select(.id == $user))})

CodePudding user response：

@knittl's solutions using INDEX are fine unless there is a "collision" of ids (e.g. if .id can be both 1 and "1").

To avoid collisions and to allow other types of .id values, you could use SAFE_INDEX and lookup defined as follows:

def SAFE_INDEX(stream; idx_expr):
  reduce stream as $row ({};
    ($row|idx_expr) as $ix
    | .[$ix|type][$ix|tostring] = $row);

def lookup($value):
   .[$value|type] as $t
   | if ($t|type) == "object" then $t[$value|tostring] else null end;

So a generic solution to the problem would look like this:

SAFE_INDEX(.users[]; .id) as $idx 
| .tweets
| map({ tweet, user: (.user as $user | $idx |lookup($user)) })