Naveen MC
Naveen MC
PPrisma
Created by Naveen MC on 11/18/2024 in #help-and-questions
Best way to look for text in a deeply nested JSON?
Hope it'll help someone ✨
9 replies
PPrisma
Created by Naveen MC on 11/18/2024 in #help-and-questions
Best way to look for text in a deeply nested JSON?
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
9 replies
PPrisma
Created by Naveen MC on 11/18/2024 in #help-and-questions
Best way to look for text in a deeply nested JSON?
So doing prisma raw SQL query will help?
9 replies
PPrisma
Created by Naveen MC on 11/18/2024 in #help-and-questions
Best way to look for text in a deeply nested JSON?
Hi @RaphaelEtim Thanks for you input. Is there any resources I can look at.
9 replies