JSON output example

[aborruso]

Hi @lmangani ,
in the documentation there is this example

SET VARIABLE openprompt_api_url = 'http://localhost:11434/v1/chat/completions';
SET VARIABLE openprompt_model_name = 'llama3.2:3b';

SELECT open_prompt('I want ice cream', json_schema := '{
       "type": "object",
       "properties": {
         "summary": { "type": "string" },
         "sentiment": { "type": "string", "enum": ["pos", "neg", "neutral"] }
       },
       "required": ["summary", "sentiment"],
       "additionalProperties": false
     }') output;

If I run it, it seems to me that it does not work. I have in output

output = { "Treating yourself to a cool treat sounds like a great idea! What's your favorite flavor of ice cream? We could chat about all the delicious options out there, from classic vanilla to unique flavors like matcha or strawberry balsamic. Or maybe you're in the mood for something creamy and cookie dough-filled?"

    :
            -0.0103
         }

Thank you

[lmangani]

Not sure here. The implementation mimicks this: https://ollama.com/blog/structured-outputs
I've tested the documented example with Ollama locally and it produces JSON output. Will do more testing!

[aborruso]

Not sure here. The implementation mimicks this: ollama.com/blog/structured-outputs
I've tested the documented example with Ollama locally and it produces JSON output. Will do more testing!

In ollama, it works. I run

curl -X POST http://localhost:11434/api/chat -H "Content-Type: application/json" -d '{
  "model": "llama3.2:3b",
  "messages": [{"role": "user", "content": "I want ice cream."}],
  "stream": false,
  "format": {
    "type": "object",
    "properties": {
      "summary": {
        "type": "string"
      },
      "sentiment": {
        "type": "string",
        "enum": ["pos", "neg", "neutral"]
      }
    },
    "required": [
      "summary",
      "sentiment"
    ],
    "additionalProperties": false
  }
}'

and I get

{
  "model": "llama3.2:3b",
  "created_at": "2025-01-11T22:03:00.894047473Z",
  "message": {
    "role": "assistant",
    "content": "{ \"summary\": \"Ice Cream\", \"sentiment\": \"neutral\" }"
  },
  "done_reason": "stop",
  "done": true,
  "total_duration": 723678490,
  "load_duration": 22122970,
  "prompt_eval_count": 30,
  "prompt_eval_duration": 4000000,
  "eval_count": 20,
  "eval_duration": 694000000
}

[lmangani]

That's interesting - what were you using in the earlier failing test? Perhaps there's some other extension at play?

[aborruso]

That's interesting - what were you using in the earlier failing test? Perhaps there's some other extension at play?

Ciao @lmangani , I am using the version compiled from your repo and so I guess without any other extension.

But I didn't understand one thing: if you run the example query does it work for you?

[lmangani]

@aborruso sorry i was referring to ollama/JSON not duckdb/extension. I only tested w/ ollama (various models)

[aborruso]

sorry i was referring to ollama/JSON not duckdb/extension. I only tested w/ ollama (various models)

Ok, as soon as you have some time, please test with this duckdb extension. I refer to this one here :)

[lmangani]

Sorry i'm not following.... I've tested this extension w/ ollama (various models) locally. I do not have any other setups or APIs to test with. In my test the JSON response only works w/ Ollama and llama3.x while for other models such as qwen I have to use the alternative (and sometimes unreliable) method of prompt based JSON schema injection.

[aborruso]

Ciao @lmangani , I can't explain myself. I start from the beginning again.

In the usage section of the README of this extension, there is an example about "JSON Structured Output".

If I run it inside duckdb cli using open_prompt function, it does not work.

If I do the same query, outside of duckdb, pointing to the same ollama server and the same model via curl, it works instead.

Does it work for you from inside the duckdb cli?

I hope I have explained myself this time :(

I am using exactly the same query as the README.

Thank you

[lmangani]

I understand now. The json_schema validation was provided by another user, so I'll retest it again next week to see what's wrong or if its just about the model capabilities. Thanks for the report!

[aborruso]

Hi @lmangani it should not be the model. Because the model responds correctly to the same call if made by curl. It seems to me that the problem is in the dialog between the extension and ollama.

[lmangani]

@aborruso if you can please provide the curl example as reference and i'll compare the two requests

[aborruso]

I wrote it just above
#21 (comment)

[lmangani]

I think the APIs are different (/api/chat vs /v1/chat/completions)

For the completions API the specs are more of less the following

To activate JSON mode, provide the response_format parameter to the Chat Completions API with {"type": "json_object"}. The JSON Schema can be specified with the schema property of response_format.

curl -X POST https://api.together.xyz/v1/chat/completions \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $TOGETHER_API_KEY" \
  -d '{
  "messages": [
    {
      "role": "system",
      "content": "The following is a voice message transcript. Only answer in JSON."
    },
    {
      "role": "user",
      "content": "Good morning! It'"'"'s 7:00 AM, and I'"'"'m just waking up. Today is going to be a busy day, so let'"'"'s get started. First, I need to make a quick breakfast. I think I'"'"'ll have some scrambled eggs and toast with a cup of coffee. While I'"'"'m cooking, I'"'"'ll also check my emails to see if there'"'"'s anything urgent."
    }
  ],
  "model": "meta-llama/Meta-Llama-3.1-8B-Instruct-Turbo",
  "response_format": {
    "type": "json_object",
    "schema": {
      "properties": {
        "title": {
          "description": "A title for the voice note",
          "title": "Title",
          "type": "string"
        },
        "summary": {
          "description": "A short one sentence summary of the voice note.",
          "title": "Summary",
          "type": "string"
        },
        "actionItems": {
          "description": "A list of action items from the voice note",
          "items": { "type": "string" },
          "title": "Actionitems",
          "type": "array"
        }
      },
      "required": ["title", "summary", "actionItems"],
      "title": "VoiceNote",
      "type": "object"
    }
  }
}'