Is there any way to rebuild the SQL Field Index Tables ?

[hung-doan]

Let's say I created a content type say "Post", this post has 1 single text field is "Description".
I created about 100 records. At this time, there is no enabled indexing.

So now, I update my content type to add the "Description" text filed to the Index via the SQL index Tables (In this case [TextFieldIndex] table).

Currently

• The Index is not rebuilt to include old contents

Expectation

• There should be a tool to rebuild the SQL Field index Tables (just like the way lucence has a rebuild button).

Does the current version of OrchardCore support this kind of feature ?.

[Piedone]

Can you specify what exactly do you mean by SQL index? Because I have a hunch we're not talking about the same thing. If you add an SQL index, as in, with CREATE INDEX, then that'll include existing rows too. But this is not something Orchard deals with but rather the respective SQL DB you're using. The same happens when you write SchemaBuilder.AlterTable("MyTable", table => table.CreateIndex(...)) in an Orchard migration.

[Skrypt]

@Piedone What he means is if there is a way to repopulate the data in his Index tables. Currently we can't do that. I personally use SQL queries to do so but we certainly miss a feature to repopulate these tables just like we can with a Lucene index.

[Piedone]

Ah, and Index table. Yeah, that's problematic.

[hung-doan]

thanks @Skrypt, I changed the title & description to prevent the confusion .
@Piedone that is exactly what I mean. I want to rebuild the [***FieldIndex] Table.

@Skrypt It would be great if you can share the script to repopulate these tables or the direction to do that ?.
The content data is serialized into the json string, I guess we have to scan the [dbo].[Document] and then use JSON_VALUE function (or some sort of function) to extract the json value and write them to the index table.

[Skrypt]

This example is for Microsoft SQL Server only. The reason why we did not implement yet a solution is because the JSON functions differ from one DB to an other.

TRUNCATE TABLE YourIndex; -- flush all data in the table and reset auto increment number of the primary key index

INSERT INTO [dbo].[YourIndex]
           ([DocumentId]
           ,[Model]
           ,[Category]
           ,[Regions]
SELECT Id AS DocumentId, 
JSON_VALUE(Content, '$.MotorEquipment.Model.Text') AS Model, -- single value field
JSON_VALUE(Content, '$.MotorEquipment.Category.LocalizationSets[0]') AS Category, -- multi-value field, retrieve first value
JSON_QUERY(Content, '$.MotorEquipment.Region.LocalizationSets') AS Regions -- multi-value field, retrieve an array of values example : ["4y115kyq4cx6w5ve6tw28sea55", "48yxzp5t8tpdh6752q5cc2wpn3"]
FROM Document
WHERE Type = 'OrchardCore.ContentManagement.ContentItem, OrchardCore.ContentManagement.Abstractions'
AND JSON_VALUE(Content, '$.ContentType') = 'MotorEquipment'

[deanmarcussen]

Related issue and suggested solution is here #5586

@hung-doan the trivial way to do this is simply republish your content items.

The slightly less trivial way is to query them with YesSql and resave them which will trigger indexing.