fix: replace .distinct() with .group_by(Conversation.id) for PostgreSQL JSON compatibility (#36610)

Co-authored-by: cocoon <kuishou68@users.noreply.github.com>
Co-authored-by: Asuka Minato <i@asukaminato.eu.org>
Co-authored-by: autofix-ci[bot] <114827586+autofix-ci[bot]@users.noreply.github.com>
(cherry picked from commit e617435d03)

api/controllers/console/app/conversation.py

@@ -137,7 +137,7 @@ class CompletionConversationApi(Resource):
                 .join(  # type: ignore
                     MessageAnnotation, MessageAnnotation.conversation_id == Conversation.id
                 )
-                .distinct()
+                .group_by(Conversation.id)
             )
         elif args.annotation_status == "not_annotated":
             query = (
@@ -275,7 +275,7 @@ class ChatConversationApi(Resource):
                     .join(  # type: ignore
                         MessageAnnotation, MessageAnnotation.conversation_id == Conversation.id
                     )
-                    .distinct()
+                    .group_by(Conversation.id)
                 )
             case "not_annotated":
                 query = (

api/controllers/console/datasets/datasets_segments.py

@@ -159,9 +159,12 @@ class DatasetDocumentSegmentListApi(Resource):
             # Use database-specific methods for JSON array search
             if dify_config.SQLALCHEMY_DATABASE_URI_SCHEME == "postgresql":
                 # PostgreSQL: Use jsonb_array_elements_text to properly handle Unicode/Chinese text
+                # Guard with jsonb_typeof to avoid "cannot extract elements from a scalar" error
+                # when keywords is null or a non-array JSON value.
                 keywords_condition = func.array_to_string(
                     func.array(
                         select(func.jsonb_array_elements_text(cast(DocumentSegment.keywords, JSONB)))
+                        .where(func.jsonb_typeof(cast(DocumentSegment.keywords, JSONB)) == "array")
                         .correlate(DocumentSegment)
                         .scalar_subquery()
                     ),