Get XML metadata into the dataset view

[rshewitt]

User Story

In order to ensure documents can be converted to ts_vector, datagov wants to investigate why certain documents are incompatible.

Acceptance Criteria

[ACs should be clearly demoable/verifiable whenever possible. Try specifying them using BDD.]

• GIVEN a json document with invalid characters preventing search vectorization
  WHEN a solution is found
  THEN the document can be converted to ts_vector
  [AND optionally another verifiable outcome]

Background

• creating the "dataset" materialized view includes converting dcatus docs to ts_vector types.
• 188 transformed ISO docs on dev contain the character "<" which postgres can't convert. Here's an example description "NOAA Central Library: <3 > v.; ill. (some col.), maps; 28 cm" which throws an error.

Security Considerations (required)

[Any security concerns that might be implicated in the change. "None" is OK, just be explicit here!]

Sketch

[neilmb]

Just FYI, I think that we won't be feeding entire JSON docs into to_tsvector when #5498 is implemented. We'll be de-structuring particular fields from the dcat column and using them in concatenating multiple ts_vector "documents" with weighting.

[rshewitt]

Just FYI, I think that we won't be feeding entire JSON docs into to_tsvector when #5498 is implemented. We'll be de-structuring particular fields from the dcat column and using them in concatenating multiple ts_vector "documents" with weighting.

understood, but what happens when invalid characters are in fields we're vectorizing? that's the case here

[neilmb]

That is not the whole story about what is happening:

SELECT to_tsvector('<3 >');
 to_tsvector
-------------
 '3':1
(1 row)

Or

SELECT '<3 >' :: TSVECTOR;
 tsvector
----------
 '<3' '>'
(1 row)

There's something else up with casting the entire source_raw column as a TSVECTOR.

[rshewitt]

cgawsbrokerprodj4m4odcoqts585e=> SELECT '<3 >' :: JSONB :: TSVECTOR;
ERROR:  invalid input syntax for type json
LINE 1: SELECT '<3 >' :: JSONB :: TSVECTOR;
               ^
DETAIL:  Token "<" is invalid.
CONTEXT:  JSON data, line 1: <...

[tdlowden]

from @rshewitt: "may need to update the data type for the record model instead". Whichever path gets the metadata that was ISO into the dataset view is 👍