How to access json objects

Elastic Stack Logstash
1

I cannot figure out how to access an array or flatten it.

I get input from an SQL database. I use jdbc_streaming to get more data

jdbc_streaming {
  jdbc_connection_string => "jdbc:postgresql://localhost:5432/db"
  statement => "SELECT user_id FROM posts WHERE post_id = :post_id"
  parameters => {"post_id" => "post_id"}
  target => "user_id"
}

This creates this absolutely ridiculous object that keys against what is returned by my database
So the return object looks like this

{
  "user_id": "00000000-0000-0000-0000-000000000000"
}

But that is ok. The problem is I now want to get the username with another lookup

jdbc_streaming {
  jdbc_connection_string => "jdbc:postgresql://localhost:5432/db"
  statement => "SELECT username FROM users WHERE user_id = :user_id"
  parameters => {"user_id" => "user_id"}
  target => "username"
}

Obviously this doesn't work because it sends
SELECT username FROM users WHERE user_id = (user_id = '00000000-0000-0000-0000-000000000000')

So then how do I access my user id?
parameters => {"user_id" => "user_id"}
parameters => {"user_id" => "%{[user_id]}"}
parameters => {"user_id" => "%{[user_id][0][user_id]}"}
parameters => {"user_id" => "%{[user_id][user_id]}"}

None of these work.
How am I supposed to do this
OR - how do I normalise the user_id field so that instead of looking like this

{
  "user_id": "00000000-0000-0000-0000-000000000000"
}

it is just
00000000-0000-0000-0000-000000000000

2

So the return object looks like this

{
 post_id: 00000000-0000-0000-0000-000000000000
}

That's not valid JSON, so just to be clear, do you mean

{
  "post_id": "00000000-0000-0000-0000-000000000000"
}

? Or, given what you write later in your post,

{
  "user_id": "00000000-0000-0000-0000-000000000000"
}

?

So then how do I access my user id?
parameters => {"user_id" => "user_id"}
parameters => {"user_id" => "%{[user_id]}"}
parameters => {"user_id" => "%{[user_id][0][user_id]}"}
parameters => {"user_id" => "%{[user_id][user_id]}"}

Probably with [user_id][post_id] or [user_id][user_id] depending on what exactly the field contains.

3

It would be easier to answer this if you showed us what you get from

output { stdout { codec => rubydebug } }
4

Sorry, made some silly mistakes, switching post_id and user_id - correct now

6

parameters => {"user_id" => "[user_id][user_id]"}

Does not work either.
Always the query at my database is
SELECT username FROM users WHERE user_id = NULL

7

Please show the result of an stdout { codec => rubydebug } output.

8

It looks like this

{
  "tags" => [
    [0] "_jdbcstreamingdefaultsused"
  ],
  "@timestamp" => 2018-08-09T13:45:06.449Z,
  "type" => "post_data",
   "created" => 2018-01-27T12:40:55.405Z,
   "@version" => "1",
   "user_id" => [
     [0] {
       "user_id" => "c99e074b-f328-48b5-89f8-4993ae1c4d3d"
     }
   ],
   "username" => [
     [0] {}
  ]
}
9

Okay. Then I'd expect [user_id][0][user_id] to work. If it doesn't I don't know what the problem is.

Wouldn't another option be to adjust the SQL query to flatten the contents of the column?

10

The SQL query is flat
It returns a single column with a single value

  • I don't know how to get logstash to store only the value

^ But that did the trick though -> [user_id][0][user_id] works
It isn't clear to me when %{[user_id][0][user_id] } should be used

  • Thanks!
1 Like
11

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.