Prisma•4d ago

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•3d 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 MCOP•3d ago

Hi @RaphaelEtim Thanks for you input. Is there any resources I can look at.

RaphaelEtim•2d 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 MCOP•2d ago

So doing prisma raw SQL query will help?

Solution

Naveen MC•2d 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•2d ago

Thanks for sharing your solution 🚀

Naveen MCOP•2d ago

Hope it'll help someone ✨

Gaming

Programming

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