I’m working with a product database that has location codes stored in a nested JSON format. I want to filter products based on specific locations, like ‘South3’. Here’s what my table looks like:
product_id | location_codes
----------+------------------------------------------------------
A01 | { "WEST": ["west1"], "EAST": ["east1"], "SOUTH": ["south1","south2","south3"]}
I’ve tried flattening the data with this query:
SELECT item.product_id, flattened.*
FROM mytable AS item
INNER JOIN LATERAL FLATTEN (input => item.location_codes) AS flattened
It gives me a result with separate rows for each region. But now I’m stuck. How can I filter this to show only the ‘South’ codes? And how would I narrow it down to just ‘south2’?
I’m new to working with nested JSON in SQL, so any tips on using FLATTEN effectively would be great. Thanks for your help!
hey! have u thought about using JSON_EXTRACT_PATH_TEXT? it might make things easier. like:
SELECT product_id, JSON_EXTRACT_PATH_TEXT(location_codes, ‘SOUTH’) as south_codes
FROM mytable
WHERE JSON_EXTRACT_PATH_TEXT(location_codes, ‘SOUTH’) LIKE ‘%south2%’
this gets just the south stuff. what do u think? could it work for ya?
To filter nested JSON data for specific locations, you can leverage the FLATTEN function with additional parameters. Here’s an approach that should work for your scenario:
SELECT product_id, f.value
FROM mytable,
LATERAL FLATTEN(input => location_codes, outer => true, path => ‘SOUTH’) f
WHERE f.value ILIKE ‘south3’
This query will specifically target the ‘SOUTH’ array within your JSON structure. The ‘outer => true’ parameter ensures you get results even if the ‘SOUTH’ key doesn’t exist for some records. Adjust the WHERE clause to filter for your desired location code.
For more flexibility, you could also use a parameter:
SELECT product_id, f.value
FROM mytable,
LATERAL FLATTEN(input => location_codes, outer => true) f
WHERE f.path[0]::string = ‘SOUTH’ AND f.value ILIKE ‘south3’
This allows you to easily change the region and specific location you’re filtering for.
hey there! i’ve dealt with similar stuff before. have u tried using the PATH option with FLATTEN? something like this might work:
SELECT product_id, f.value
FROM mytable,
LATERAL FLATTEN(input => location_codes, path => ‘SOUTH’) f
WHERE f.value = ‘south2’
this should grab just the south codes n let u filter for south2. lemme know if that helps!