Query Json Column

Pooja

Hi there,

I was exploring xano, in my table, I've column type json, I want to query that column, lets the column stores data like tags e.g. ["xano","lowcode"], so if I query "xano" it should return all the records having tag xano 

Ray Deck

I do this with the "in" operator in your custom query filter from "query all records". Here's a quick screenshot of a sample. You get there by adding a function,  database manipulation, query all records, and then "by custom query" at which point you can add this filter:[Screen Shot 2022-03-04 at 7.54.52 AM.png]

Pooja

Hi , thank you for the reply

I've tried with "in" operator, but it's showing me an error, here's a screenshot for reference : 
[xano-custom-function.png][Xano-debug-error.png]

Ray Deck

What is the type of the table? If you go to  database, select your table, there is a button, "show schema" above the first row. Can you click it and share a screenshot? 

Pooja

[xano-table-schema.png]

Ray Deck

Got it. Given your structure, could you do tags as type text and select "list" instead of "single"? That would make it much easier to query using "in", since one side would be "known" to be an array. It's also a very common pattern for storing string-based tags, which I think is your use case

Pooja

That table I created for demo purpose only. I want to keep the column type to JSON. Does that mean Querying Json Column is not supported? 

Ray Deck

I walked through how to do this in a Loom video because its a little more involved than just using the query parameter AFAIK. Hope it helps!  https://www.loom.com/share/335e6fe4e5b6438ba5d63b925346c35e

Pooja

Thank you  , for walking me through how to achieve this, this was really helpful.   

Ray Deck

 I'm so glad! Let me know if I can help further.