Having JSON trouble with Nocodb API (escaping special characters?)

I don’t think its even required but no matter what I try I can’t seem to escape special characters like ? which are saved into the db as $.

I don’t believe I even have to escape ? - but they’re not saving as expected…

Below is an example of the saved json and the ? issue:

  {
    "role": "user",
    "content": "$1"
  },
  {
    "role": "assistant",
    "content": "Hello! How can I assist you today$2"
  },
  {
    "role": "user",
    "content": "hey"
  },
  {
    "role": "assistant",
    "content": "Hello! How can I assist you today$1"
  }

Below is my script for saving the json message:

const requestBody = {
      user_id: userId,
      messages: JSON.stringify(messages),
      files_id: isFile ? itemId : null,
      folders_id: !isFile ? itemId : null,
    };

    console.log('Request body before stringification:', requestBody);
    console.log('Stringified messages:', requestBody.messages);

    const createResponse = await fetch(`${BASE_URL}/${CONVERSATIONS_TABLE_ID}/records`, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
        'xc-token': process.env.NEXT_PUBLIC_NOCODB_AUTH_TOKEN,
      },
      body: JSON.stringify(requestBody),
    });

Any help is appreciated! Thanks

Can you share more details,

  • What’s the api response you are getting ?
  • Console log output of the code.

I’ll try my best…

But in my logs, I can see the current state of the conversation between user and assistant as:

42
: 
{role: 'user', content: 'Hi?'}
43
: 
{role: 'assistant', content: 'Hello! How can I assist you today?'}

then somewhere it is saved with $ instead of ?, and thus the below is fetched:

Message 42 contains unexpected '$': {role: 'user', content: 'Hi$1'}
AIResponse.jsx:41

Message 43 contains unexpected '$': {role: 'assistant', content: 'Hello! How can I assist you today$2'}

The issue is not clear, Does the insert API call fails ? Can you check createResponse and confirm the response?

looks like those logs aren’t even showing in my console either… Working on fixing that now.

I can share the rest of my route.js script above? I think I’ve narrowed it down to that, its the api route for my PATCH, and POST

Check the API response of these calls as well.

The PATCH API response shows question marks correctly

Messages to be updated: [
  {
    "role": "user",
    "content": "no?"
  },
  {
    "role": "assistant",
    "content": "I'm sorry, could you please provide more context or clarification so I can better assist you?"
  }
]
Update request body: {
  "Id": 91,
  "messages": "[{\"role\":\"user\",\"content\":\"no?\"},{\"role\":\"assistant\",\"content\":\"I'm sorry, could you please provide more context or clarification so I can better assist you?\"}]"
}
Update response status: 200
Update response body as text: {"Id":91}
Updated conversation: {
  "Id": 91,
  "Title": null,
  "CreatedAt": "2024-09-11 11:44:09+00:00",
  "UpdatedAt": "2024-09-14 01:45:00+00:00",
  "messages": [
    {
      "role": "user",
      "content": "no$1"
    },
    {
      "role": "assistant",
      "content": "I'm sorry, could you please provide more context or clarification so I can better assist you$2"
    }
  ],
  "user_id": 386,
  "files_id": 472,
  "folders_id": null,
  "folders": null,
  "files": {
    "Id": 472
  },
  "user": {
    "Id": 386
  }
}
Saved messages: [
  {
    "role": "user",
    "content": "no$1"
  },
  {
    "role": "assistant",
    "content": "I'm sorry, could you please provide more context or clarification so I can better assist you$2"
  }
]
 PATCH /api/chat/history 200 in 1759ms

Note:

  • Update response status: 200 is showing as an orange 200

this workaround using the below in my PATCH works, but breaks another part of my functionality so don’t want to have to use it…

// Encode special characters before saving
    const encodedMessages = messages.map(msg => ({
      ...msg,
      content: msg.content.replace(/\?/g, '{{QUESTION_MARK}}')
    }));

    const updateBody = { 
      Id: conversationId,
      messages: JSON.stringify(encodedMessages),
    };

and this in my GET:

// Parse and decode messages
      let decodedMessages;
      if (typeof conversation.messages === 'string') {
        decodedMessages = JSON.parse(conversation.messages);
      } else {
        decodedMessages = conversation.messages;
      }
      
      // Decode the special characters
      decodedMessages = decodedMessages.map(msg => ({
        ...msg,
        content: msg.content.replace(/{{QUESTION_MARK}}/g, '?')
      }));

      conversation.messages = decodedMessages;
      
      return NextResponse.json({ conversation });

This is my PATCH that’s hitting the issues without the above included:

export async function PATCH(req) {
  const body = await req.json();
  console.log('PATCH request body:', JSON.stringify(body, null, 2));

  const { conversationId, messages } = body;

  if (!conversationId) {
    console.error('conversationId is missing in the PATCH request');
    return NextResponse.json({ error: 'conversationId is required' }, { status: 400 });
  }

  try {
    console.log(`Updating conversation with ID: ${conversationId}`);
    console.log('Messages to be updated:', JSON.stringify(messages, null, 2));

    // Replace dollar signs with question marks
    const fixedMessages = messages.map(msg => ({
      ...msg,
      content: msg.content.replace(/\$/g, '?')
    }));

    const updateBody = { 
      Id: conversationId,
      messages: JSON.stringify(fixedMessages),
    };
    console.log('Update request body:', JSON.stringify(updateBody, null, 2));

    const updateConversationResponse = await fetch(`${BASE_URL}/${CONVERSATIONS_TABLE_ID}/records`, {
      method: 'PATCH',
      headers: {
        'Content-Type': 'application/json; charset=UTF-8',
        'xc-token': process.env.NEXT_PUBLIC_NOCODB_AUTH_TOKEN,
      },
      body: JSON.stringify(updateBody),
    });

    console.log('Update response status:', updateConversationResponse.status);
    const updateResponseText = await updateConversationResponse.text();
    console.log('Update response body as text:', updateResponseText);

    if (!updateConversationResponse.ok) {
      throw new Error(`NocoDB API responded with status ${updateConversationResponse.status}: ${updateResponseText}`);
    }

    // Fetch the updated conversation to verify the changes
    const fetchUpdatedConversationResponse = await fetch(`${BASE_URL}/${CONVERSATIONS_TABLE_ID}/records/${conversationId}`, {
      headers: {
        'xc-token': process.env.NEXT_PUBLIC_NOCODB_AUTH_TOKEN,
      },
    });

    if (!fetchUpdatedConversationResponse.ok) {
      throw new Error(`Failed to fetch updated conversation: ${fetchUpdatedConversationResponse.status}`);
    }

    const updatedConversation = await fetchUpdatedConversationResponse.json();
    console.log('Updated conversation:', JSON.stringify(updatedConversation, null, 2));

    // Parse messages if they're stored as a string
    let savedMessages;
    if (typeof updatedConversation.messages === 'string') {
      savedMessages = JSON.parse(updatedConversation.messages);
    } else {
      savedMessages = updatedConversation.messages;
    }
    console.log('Saved messages:', JSON.stringify(savedMessages, null, 2));

    return NextResponse.json({ success: true, updatedConversation: { ...updatedConversation, messages: savedMessages } });
  } catch (error) {
    console.error('Error updating conversation:', error);
    return NextResponse.json({ error: 'Failed to update conversation', details: error.message }, { status: 500 });
  }
}

Thanks for sharing the details. Hope it’s related to knex, if ? prsent in query knex/client-library may be treating it as dynamic parameters and replacing it with $1, $2,…etc. I will look into it and get back to you soon.

1 Like

Couple more queries,

  • Are you using our cloud service ?
  • If not which version of docker are you using ?

@pranavxc cloud service

We’ve identified and resolved the issue with the update API. The fix has been deployed to the cloud, and you can now test it.

Thanks! Confirmed working on my side!

3 Likes