P
Prisma•4w ago
Naveen MC

Best way to look for text in a deeply nested JSON?

I have a JSON field in a table. Which holds the metadata for the row. So what will be in the JSON is kind of unpredictable. Because it'll store tiptap editor json, other json etc., Here are the few examples Example 1:
{
"content": [
{
"children": [],
"content": [
{
"styles": {},
"text": "Rocket Man",
"type": "text"
}
],
"id": "c1ef50f8-5cbd-431f-bdd5-0b3a346a850f",
"props": {
"backgroundColor": "default",
"level": 1,
"textAlignment": "left",
"textColor": "default"
},
"type": "heading"
},
{
"children": [],
"content": [
{
"styles": {},
"text": "Rocket man is good person",
"type": "text"
}
],
"id": "2d62b7cf-f945-47e0-b5ea-00e23499bde0",
"props": {
"backgroundColor": "default",
"textAlignment": "left",
"textColor": "default"
},
"type": "paragraph"
},
{
"children": [],
"content": [],
"id": "8993a2c6-4ff3-4c82-a50a-724b3d07ed82",
"props": {
"backgroundColor": "default",
"textAlignment": "left",
"textColor": "default"
},
"type": "paragraph"
}
]
}
{
"content": [
{
"children": [],
"content": [
{
"styles": {},
"text": "Rocket Man",
"type": "text"
}
],
"id": "c1ef50f8-5cbd-431f-bdd5-0b3a346a850f",
"props": {
"backgroundColor": "default",
"level": 1,
"textAlignment": "left",
"textColor": "default"
},
"type": "heading"
},
{
"children": [],
"content": [
{
"styles": {},
"text": "Rocket man is good person",
"type": "text"
}
],
"id": "2d62b7cf-f945-47e0-b5ea-00e23499bde0",
"props": {
"backgroundColor": "default",
"textAlignment": "left",
"textColor": "default"
},
"type": "paragraph"
},
{
"children": [],
"content": [],
"id": "8993a2c6-4ff3-4c82-a50a-724b3d07ed82",
"props": {
"backgroundColor": "default",
"textAlignment": "left",
"textColor": "default"
},
"type": "paragraph"
}
]
}
Example 2:
{
"attending": [
"cm38i9jrt000014pcg0lw2t29"
],
"date": "0003-08-09",
"description": "Rocket Launch",
"invited": [],
"location": "Rocket Spot",
"organizer": "cm38i9jrt000014pcg0lw2t29",
"rsvp": [],
"title": "Rocket Man"
}
{
"attending": [
"cm38i9jrt000014pcg0lw2t29"
],
"date": "0003-08-09",
"description": "Rocket Launch",
"invited": [],
"location": "Rocket Spot",
"organizer": "cm38i9jrt000014pcg0lw2t29",
"rsvp": [],
"title": "Rocket Man"
}
Example 3:
{
"type": "POLL",
"body": "What's your favorite rocket color?",
"meta": {
"pollOptions": [
{
"text": "Option 1 Rocket",
"userId": []
},
{
"text": "Option 2 Rocket",
"userId": []
}
],
"pollEndTime": "2024-02-01T00:00:00.000Z"
}
}
{
"type": "POLL",
"body": "What's your favorite rocket color?",
"meta": {
"pollOptions": [
{
"text": "Option 1 Rocket",
"userId": []
},
{
"text": "Option 2 Rocket",
"userId": []
}
],
"pollEndTime": "2024-02-01T00:00:00.000Z"
}
}
What's the efficient way to handle the search? Example search term is "Rocket"
Solution:
Okay, I did this. `` const postsWithMeta = await db.$queryRaw<Posts[]> SELECT p.* FROM "Posts" p...
Jump to solution
7 Replies
RaphaelEtim
RaphaelEtim•3w ago
Hi @Naveen MC Since the JSON field has an unpredictable structure, i think using database specific JSON search function through either raw query of typedsql should work. This would also allow search across all possible locations in the json field.
Naveen MC
Naveen MCOP•3w ago
Hi @RaphaelEtim Thanks for you input. Is there any resources I can look at.
RaphaelEtim
RaphaelEtim•3w ago
Hi @Naveen MC For PostgreSQL, you could use the jsonb_path_query_array function, which allows you to search across all possible locations in the JSON field. For example:
SELECT *
FROM your_table
WHERE jsonb_path_query_array(metadata, '$.** ? (@.type() == "string" && @ like_regex "Rocket" flag "i")') != '[]';
SELECT *
FROM your_table
WHERE jsonb_path_query_array(metadata, '$.** ? (@.type() == "string" && @ like_regex "Rocket" flag "i")') != '[]';
This query searches for the term "Rocket" case-insensitively across all string values in the JSON structure, regardless of their location or nesting level.
Naveen MC
Naveen MCOP•3w ago
So doing prisma raw SQL query will help?
Solution
Naveen MC
Naveen MC•3w ago
Okay, I did this.
const postsWithMeta = await db.$queryRaw<Posts[]>`
SELECT p.*
FROM "Posts" p
WHERE p."communityId" = ${communityId}
AND p.meta IS NOT NULL
AND EXISTS (
SELECT 1
FROM jsonb_each_text(CASE
WHEN jsonb_typeof(p.meta) = 'object' THEN p.meta
ELSE '{}'::jsonb
END)
WHERE value ILIKE ${`%${keyword}%`}
)
`;
const postsWithMeta = await db.$queryRaw<Posts[]>`
SELECT p.*
FROM "Posts" p
WHERE p."communityId" = ${communityId}
AND p.meta IS NOT NULL
AND EXISTS (
SELECT 1
FROM jsonb_each_text(CASE
WHEN jsonb_typeof(p.meta) = 'object' THEN p.meta
ELSE '{}'::jsonb
END)
WHERE value ILIKE ${`%${keyword}%`}
)
`;
And it seems to be working
RaphaelEtim
RaphaelEtim•3w ago
Thanks for sharing your solution 🚀
Naveen MC
Naveen MCOP•3w ago
Hope it'll help someone ✨
Want results from more Discord servers?
Add your server