https://linen.dev logo
Join Slack
Powered by
HI all, I'm investing how to create a lookup where...
# general
r
HI all, I'm investing how to create a lookup where the value is a complex object represented for example by a json, is it possible?...I tryed in this way but the value in this test is always a string...thanks
l
you can wrap the value in 
PARSE_JSON
to get Complex<json>. Something like: 
PARSE_JSON(LOOKUP(column, 'lookup_name'))
r
Do you mean in this way? SELECT "k", PARSE_JSON(LOOKUP('name', 'user')) AS name, PARSE_JSON(LOOKUP('store', 'user')) AS store FROM "lookup"."user"
The result is this:
l
what’s the lookup that you have configured? And what’s the column that you need to query.
the column name shouldn’t be quoted. Also, the query seems suspcious, you might have used incorrect column names
r
This is the lookup configured
I tried this and it works
but how can I remove the " " in the columns name and store?
l
Does something like following help? 
JSON_VALUE(PARSE_JSON("v"), '$.name' RETURNING VARCHAR)
r
YES thank you! 👍
But if I have 2 lookups (first_lookup,second_lookup) how can I differentiate if "v" belong to first_lookup instead of the second_lookup?
l
You can use the Lookup function to lookup.
Let’s say you have a table with table col1 that you want to map to the lookups. You can use LOOKUP(col1, ‘first_lookup’) to differentiate.
r
ok I will try thank you
In my case I have to use the lookups into FROM clause of a query and in this way it works: 
SELECT e.tracking_action_name, e.tracking_action_type, e.tracking_action_view, e.user_agent_device,
JSON_VALUE(PARSE_JSON(sso.v), '$.operator_code') AS "operator_code",
JSON_VALUE(PARSE_JSON(sso.v), '$.username') AS "username",
JSON_VALUE(PARSE_JSON(w.v), '$.store_name') AS "store_name",
JSON_VALUE(PARSE_JSON(w.v), '$.area_code') AS "area_code",
count(*)
FROM "user_analytics" e join lookup."sso_user" sso on sso.k = e.user_sso_id
join lookup."warehouse" w on w.k = e."user_sso_warehouse_id"
WHERE e.tracking_action_name is not null and "__time" > '2024-06-25'
GROUP BY e.tracking_action_name , e."tracking_action_type" , e."tracking_action_view", e."user_agent_device", sso.v, w.v
4 Views
Open in Slack
PreviousNext
Powered by