Working with JSON
This page describes a tutorial on how to work with JSON in Lenses SQL.
Last updated
This page describes a tutorial on how to work with JSON in Lenses SQL.
Last updated
2024 © Lenses.io Ltd. Apache, Apache Kafka, Kafka and associated open source project names are trademarks of the Apache Software Foundation.
In this tutorial, we will see how to use Lenses SQL to process JSON strings using , a sort of XPath for JSON objects.
In Lenses SQL you can use a JSON_EXTRACT_FIRST()
and JSON_EXTRACT_ALL()
to navigate and transform JSON strings.
We have a topic http_logs
that collects the details of HTTP calls to a microservice. Basic details of the request are stored, like the URL
and the HTTP
method used. The payload of the requests is stored as well as a string.
We can create the topic and insert some example data through SQL Studio:
The HTTP
method and the URL
used for the request are stored in the method
and url
fields respectively, while the optional payload, and its content-type, are stored in the body
and content_type
fields.
As you can imagine the logs contained in this topic are quite generic, and different endpoints may have different content-type
s for their body. For this reason the best the system can do is storing the payload as a simple string, whenever that is possible.
This comes with some drawbacks: since the data is a simple string, and it is not structured, it is not possible to inspect it as we would do with a normal AVRO
/JSON
object.
Fortunately Lenses SQL offers a couple of handful functions that make our life easier in these kind of scenarios.
Our first task is to find the username of users created with a call to POST /users
.
JsonPath is a powerful way to traverse and extract elements from a JSON
object. Explaining the full details of goes beyond the scope of this article, but in general it can be thought as a JSON
version of XPath
, the standard used to select elements from an XML document.
In our case, we would like to extract the name of the user just created. The simple path $.username
will do it!
Let’s try to use it in a SELECT
that we can run in SQL Studio:
That query will produce the results
As you can see we have two entries for juno
. That’s because the user was first created, and then modified later, with a PUT
call.
Also, there are some null
values. This is because JSON_EXTRACT_FIRST
was not able to extract the username, either because the payload was not valid JSON
, or because the field was not found.
We can fix this restricting our query to user creation calls:
We have now only valid results:
All Lenses SQL functions can be used in any part of the query. Thus JSON_EXTRACT_FIRST
can be used in the projections, where, and group bys.
For example, you can run the query
to retrieve max
’s e-mail:
So far we had fun using JSON_EXTRACT_FIRST
, but we have not talked yet about its bigger brother, JSON_EXTRACT_ALL
.
JSON_EXTRACT_ALL(json_string, pattern)
works like JSON_EXTRACT_FIRST
, except that it will return all the values that match the pattern
. The results will be returned in an array, and when no results are found the empty array will be returned.
Let’s make use of it, extracting all the contact types used at the moment of the creation of the user:
Running the query above we get what we desired:
JSON_EXTRACT_FIRST()
and JSON_EXTRACT_ALL()
are available also in the Streaming Engine, like most Lenses SQL functions.
Let’s say we want another topic continuously filled with the contact types used for user creations. We also want each record containing a single username-contact type pair. To achieve that we can take the query of the last example and adapt it a bit, using a lateral join.
JSON_EXTRACT_FIRST()
and JSON_EXTRACT_ALL()
simplifies your life every time you have to deal with JSON
that is represented as a string value of a field in your topic.
The use of JsonPath
make them very powerful and even complex operations are easily representable with it.
To do that we can use JSON_EXTRACT_FIRST(json_string, pattern)
, one of the string functions available in Lenses SQL. The first argument of the function is the string representing the JSON
we want to manipulate. The second is a string representing a .
A nice way to try and test if your JsonPaths are doing what you intended, is using the .