JSON extract using custom sql

Hello Quicksight guys

I have a problem with my data, here is an example of what I have:

So Basically I need to extract the ‘found’ information from this JSON column and I have three alternatives, true, false and null. My idea was to count the amount of true, false, and null and add them as a new column, so I can verify separately if a specific order was found completely or not. But I’m having problems with the SQL syntax because is giving me 0, here’s the json example:

image

The main question that I need to solve is: What orders-products Am I completing? Note that in picking_data might be more than 1 product.

Thx for reading and helping me.

Hi @Gustavo_Gutierrez

this is not about Quicksight per se but about how to work with json files.

The problem is that you use ‘true’ as a string not as a boolean. Instead of = ‘true’, you should use either = TRUE depending on your database. Same with =‘false’. What you do is like using NULL like this: =‘NULL’. But putting it in single quotes you are converting it from a special marker into a string with the value of ‘NULL’

Hope it helps,
GL

2 Likes

Hi @gillepa thx for sharing that approach, but it is not returning either the result for this particular sql problem

Well, I’ve been working on this, and finally get to a solution, the main problem is that I was trying to sum and count the different situations of my picking_data.found, so the answer to this was applying the JSON_SEARCH function which allows me to search for a string inside the JSON, but the problem was that the results were in boolean, so I did a little transformation and tah dah. Got this, and the problem is resolve. Thx @gillepa for your response, it took me to think of another path to this problem.

select
       JSON_LENGTH(JSON_SEARCH(replace(replace(
                                               replace(products_list,
                                                       '"found":true',
                                                       '"found": "PRODUCT_FOUND"'),
                                               '"found":false',
                                               '"found": "PRODUCT_NOT_FOUND"'),
                                       '"found":null',
                                       '"found": "PRODUCT_UNKNOWN_STATUS"'), 'all', 'PRODUCT_FOUND', NULL, '$.products[*].picking_data.found')) as found,
       JSON_LENGTH(JSON_SEARCH(replace(replace(
                                               replace(products_list,
                                                       '"found":true',
                                                       '"found": "PRODUCT_FOUND"'),
                                               '"found":false',
                                               '"found": "PRODUCT_NOT_FOUND"'),
                                       '"found":null',
                                       '"found": "PRODUCT_UNKNOWN_STATUS"'), 'all', 'PRODUCT_NOT_FOUND', NULL, '$.products[*].picking_data.found')) as not_found,
       JSON_LENGTH(JSON_SEARCH(replace(replace(
                                               replace(products_list,
                                                       '"found":true',
                                                       '"found": "PRODUCT_FOUND"'),
                                               '"found":false',
                                               '"found": "PRODUCT_NOT_FOUND"'),
                                       '"found":null',
                                       '"found": "PRODUCT_UNKNOWN_STATUS"'), 'all', 'PRODUCT_UNKNOWN_STATUS', NULL, '$.products[*].picking_data.found')) as unknown
from order_tickets

It is a little ugly, but Now i’m transforming it to a function to beautify the results lol.

Thanks for the help <3