Question 110242 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Json – INNER JOIN in jq

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

Tags:

2

Answers


  1. 0

    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))})
    Login or Signup to reply.
  2. 0

    @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)) })
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search