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:
Example 2:
Example 3:
What's the efficient way to handle the search? Example search term is "Rocket"
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.
And it seems to be working
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
