"SQL query" module and Postgre query: how to retrieve the user details stored in the DB using a "CustomUserSettings" Content Type?

[MarGraz]

Hi everyone,

I have added some details to the User using a CustomUserSettings content type, as explained in the documentation. Now, I'm writing a SQL query to retrieve this additional info, and I'm using a PostgreSQL DB (version 16.2).

In a previous discussion, I was unable to use the CAST() function because it is not supported in the Orchard Core editor for Postgre. Following suggestions, I implemented my own casttotext() custom function as workaround.

Now, I have a similar problem: It seems that some PostgreSQL functions and operators are not supported. I tried using #>> or json_extract_path_text() without success, although they are usually supported in Postgre, as described here.

The question is:
How can I retrieve the user details stored in the DB using a "CustomUserSettings" content type?

Here's how the info is stored in the DB, the Document table has 4 columns: Id, Type, Content, and Version. The Content column contains the info I need in a JSON string. Here's the JSON:

{
  "Id": 10,
  "UserId": "****",
  "UserName": "****",
  "NormalizedUserName": "****",
  "Email": "****",
  "NormalizedEmail": "****",
  "PasswordHash": "****",
  "SecurityStamp": "****",
  "EmailConfirmed": true,
  "PhoneNumberConfirmed": false,
  "IsEnabled": true,
  "TwoFactorEnabled": false,
  "IsLockoutEnabled": true,
  "AccessFailedCount": 0,
  "RoleNames": [
    "Administrator"
  ],
  "UserClaims": [],
  "LoginInfos": [],
  "UserTokens": [],
  "Properties": {
    "UserNotificationPreferencesPart": {
      "Methods": [
        "Email"
      ],
      "Optout": []
    },
    "UserDetails": {
      "ContentItemId": "****",
      "ContentItemVersionId": null,
      "ContentType": "UserDetails",
      "DisplayText": "",
      "Latest": false,
      "Published": false,
      "ModifiedUtc": "2024-04-16T08:26:11.7923065Z",
      "PublishedUtc": null,
      "CreatedUtc": null,
      "Owner": "",
      "Author": "TestUserAdmin",
      "UserDetails": {
        "Name": {
          "Text": "Pippo"
        },
        "Surname": {
          "Text": "Pluto"
        },
        "Phone": {
          "Text": "+39123456789"
        },
        "WhatsApp": {
          "Text": null
        },
        "Street": {
          "Text": "Via del Colosseo"
        },
        "StreetNumber": {
          "Text": "1"
        },
        "City": {
          "Text": "Rome"
        }
      }
    }
  }
}

This is the first query I tried and it doesn't work:

SELECT 
    UI.DocumentId, 
    UI.UserId, 
    UI.NormalizedUserName, 
    UI.NormalizedEmail, 
    STRING_AGG(UBRNI.RoleName, ',') AS RoleNames,
    STRING_AGG(casttotext(UBRNID.UserByRoleNameIndexId), ',') AS RoleIds,
    json_extract_path_text(D.Content, 'Properties', 'UserDetails', 'Name') AS FirstName,
    json_extract_path_text(D.Content, 'Properties', 'UserDetails', 'Surname') AS LastName,
    json_extract_path_text(D.Content, 'Properties', 'UserDetails', 'Phone') AS Phone,
    json_extract_path_text(D.Content, 'Properties', 'UserDetails', 'Street') AS Street,
    json_extract_path_text(D.Content, 'Properties', 'UserDetails', 'StreetNumber') AS StreetNumber,
    json_extract_path_text(D.Content, 'Properties', 'UserDetails', 'City') AS City
FROM 
    UserIndex UI
JOIN 
    Document D ON UI.DocumentId = D.Id
JOIN 
    UserByRoleNameIndex_Document UBRNID ON UI.DocumentId = UBRNID.DocumentId
JOIN 
    UserByRoleNameIndex UBRNI ON UBRNID.UserByRoleNameIndexId = UBRNI.Id
GROUP BY 
    UI.DocumentId, 
    UI.UserId, 
    UI.NormalizedUserName, 
    UI.NormalizedEmail,
    D.Content

The second query that doesn't work:

SELECT 
    UI.DocumentId, 
    UI.UserId, 
    UI.NormalizedUserName, 
    UI.NormalizedEmail, 
    STRING_AGG(UBRNI.RoleName, ',') AS RoleNames,
    STRING_AGG(casttotext(UBRNID.UserByRoleNameIndexId), ',') AS RoleIds,
    D.Content #>> '{Properties, UserDetails, Name}' AS FirstName,
    D.Content #>> '{Properties, UserDetails, Surname}' AS LastName,
    D.Content #>> '{Properties, UserDetails, Phone}' AS Phone,
    D.Content #>> '{Properties, UserDetails, Street}' AS Street,
    D.Content #>> '{Properties, UserDetails, StreetNumber}' AS StreetNumber,
    D.Content #>> '{Properties, UserDetails, City}' AS City
FROM 
    UserIndex UI
JOIN 
    Document D ON UI.DocumentId = D.Id
JOIN 
    UserByRoleNameIndex_Document UBRNID ON UI.DocumentId = UBRNID.DocumentId
JOIN 
    UserByRoleNameIndex UBRNI ON UBRNID.UserByRoleNameIndexId = UBRNI.Id
GROUP BY 
    UI.DocumentId, 
    UI.UserId, 
    UI.NormalizedUserName, 
    UI.NormalizedEmail,
    D.Content

The third query that works, but returns a JSON string, and is not what I want:

SELECT 
    UI.DocumentId, 
    UI.UserId, 
    UI.NormalizedUserName, 
    UI.NormalizedEmail, 
    STRING_AGG(UBRNI.RoleName, ',') AS RoleNames,
    STRING_AGG(casttotext(UBRNID.UserByRoleNameIndexId), ',') AS RoleIds,
    D.Content AS UserDetailsJSON
FROM 
    UserIndex UI
JOIN 
    Document D ON UI.DocumentId = D.Id
JOIN 
    UserByRoleNameIndex_Document UBRNID ON UI.DocumentId = UBRNID.DocumentId
JOIN 
    UserByRoleNameIndex UBRNI ON UBRNID.UserByRoleNameIndexId = UBRNI.Id
GROUP BY 
    UI.DocumentId, 
    UI.UserId, 
    UI.NormalizedUserName, 
    UI.NormalizedEmail,
    D.Content

Thank you

[MarGraz]

@lampersky maybe you have a quick solution as in the previous discussion 😊🙃🤔

@sebastienros as for the previous discussion, could be useful to add this as a feature in the generic dialect?

Thank you

[lampersky]

@MarGraz what error message did you get for first query (with json_extract_path_text), it looks good to me, I only added a few more path elements:

SELECT 
    UI.DocumentId, 
    UI.UserId, 
    UI.NormalizedUserName, 
    UI.NormalizedEmail, 
    STRING_AGG(UBRNI.RoleName, ',') AS RoleNames,
    STRING_AGG(casttotext(UBRNID.UserByRoleNameIndexId), ',') AS RoleIds,
    json_extract_path_text(D.Content, 'Properties', 'UserDetails', 'UserDetails', 'Name', 'Text') AS FirstName,
    json_extract_path_text(D.Content, 'Properties', 'UserDetails', 'UserDetails', 'Surname', 'Text') AS LastName,
    json_extract_path_text(D.Content, 'Properties', 'UserDetails', 'UserDetails', 'Phone', 'Text') AS Phone,
    json_extract_path_text(D.Content, 'Properties', 'UserDetails', 'UserDetails', 'Street', 'Text') AS Street,
    json_extract_path_text(D.Content, 'Properties', 'UserDetails', 'UserDetails', 'StreetNumber', 'Text') AS StreetNumber,
    json_extract_path_text(D.Content, 'Properties', 'UserDetails', 'UserDetails', 'City', 'Text') AS City
FROM 
    UserIndex UI
JOIN 
    Document D ON UI.DocumentId = D.Id
JOIN 
    UserByRoleNameIndex_Document UBRNID ON UI.DocumentId = UBRNID.DocumentId
JOIN 
    UserByRoleNameIndex UBRNI ON UBRNID.UserByRoleNameIndexId = UBRNI.Id
GROUP BY 
    UI.DocumentId, 
    UI.UserId, 
    UI.NormalizedUserName, 
    UI.NormalizedEmail,
    D.Content

[MarGraz]

@lampersky thank you for your reply.

From the first query, I get this error:

An error occurred while executing the SQL query: 42883: function json_extract_path_text(text, unknown, unknown, unknown) does not exist POSITION: 205

Unfortunately I get the same error with your query 😟

An error occurred while executing the SQL query: 42883: function json_extract_path_text(text, unknown, unknown, unknown, unknown, unknown) does not exist POSITION: 205

Thank you

[lampersky]

An error occurred while executing the SQL query: 42883: function json_extract_path_text(text, unknown, unknown, unknown) does not exist POSITION: 205

@MarGraz this is an error from postgreSQL not from parser, which means the query is grammatically correct, I don't have postgreSQL instance to test, please first try your query with pgAdmin, other than that make sure your postgreSQL version supports this json function

[MarGraz]

@lampersky yes, I try my queries in pgAdmin first, but what works in pgAdmin, unfortunately doesn't always work using the Orchard Core SQL query editor 😟

For example, this query works perfectly in pgAdmin:

SELECT 
    UI."DocumentId", 
    UI."UserId", 
    UI."NormalizedUserName", 
    UI."NormalizedEmail", 
    STRING_AGG(UBRNI."RoleName", ',') AS "RoleNames",
    STRING_AGG(casttotext(UBRNID."UserByRoleNameIndexId"), ',') AS "RoleIds",
    jsonb_extract_path_text(D."Content"::jsonb, 'Properties', 'UserDetails', 'UserDetails', 'Name', 'Text') AS "FirstName",
    jsonb_extract_path_text(D."Content"::jsonb, 'Properties', 'UserDetails', 'UserDetails', 'Surname', 'Text') AS "LastName",
    jsonb_extract_path_text(D."Content"::jsonb, 'Properties', 'UserDetails', 'UserDetails', 'Phone', 'Text') AS "Phone",
    jsonb_extract_path_text(D."Content"::jsonb, 'Properties', 'UserDetails', 'UserDetails', 'Street', 'Text') AS "Street",
    jsonb_extract_path_text(D."Content"::jsonb, 'Properties', 'UserDetails', 'UserDetails', 'StreetNumber', 'Text') AS "StreetNumber",
    jsonb_extract_path_text(D."Content"::jsonb, 'Properties', 'UserDetails', 'UserDetails', 'City', 'Text') AS "City"
FROM 
    "UserIndex" UI
JOIN 
    "Document" D ON UI."DocumentId" = D."Id"
JOIN 
    "UserByRoleNameIndex_Document" UBRNID ON UI."DocumentId" = UBRNID."DocumentId"
JOIN 
    "UserByRoleNameIndex" UBRNI ON UBRNID."UserByRoleNameIndexId" = UBRNI."Id"
GROUP BY 
    UI."DocumentId", 
    UI."UserId", 
    UI."NormalizedUserName", 
    UI."NormalizedEmail",
    D."Content"

But doesn't work in Orchard Core, it return an error. I always need to first fix the syntax, like removing all the "", and then try again.

Thank you

[MarGraz]

I tried to change the syntax of the previous query that works in pgAdmin, but in Orchard Core I get the error:

Syntax error, expected: ., (, +, -, *, /, %, &, |, ^, =, >, <, >=, <=, <>, !=, !<, !>, AND, OR, LIKE, NOT LIKE, NOT, BETWEEN, IN, ,, ), GROUP, HAVING, ORDER, LIMIT, OFFSET, UNION, ;, WITH, SELECT, INNER, LEFT, RIGHT, JOIN, WHERE at line:7, col:37

There is something in this line that make Orchard Core returning an error (probably the :: casting):

jsonb_extract_path_text(D.Content::jsonb, 'Properties', 'UserDetails', 'UserDetails', 'Name', 'Text') AS FirstName,

This is the syntax adjusted query:

SELECT 
    UI.DocumentId, 
    UI.UserId, 
    UI.NormalizedUserName, 
    UI.NormalizedEmail, 
    STRING_AGG(UBRNI.RoleName, ',') AS RoleNames,
    STRING_AGG(casttotext(UBRNID.UserByRoleNameIndexId), ',') AS RoleIds,
    jsonb_extract_path_text(D.Content::jsonb, 'Properties', 'UserDetails', 'UserDetails', 'Name', 'Text') AS FirstName,
    jsonb_extract_path_text(D.Content::jsonb, 'Properties', 'UserDetails', 'UserDetails', 'Surname', 'Text') AS LastName,
    jsonb_extract_path_text(D.Content::jsonb, 'Properties', 'UserDetails', 'UserDetails', 'Phone', 'Text') AS Phone,
    jsonb_extract_path_text(D.Content::jsonb, 'Properties', 'UserDetails', 'UserDetails', 'Street', 'Text') AS Street,
    jsonb_extract_path_text(D.Content::jsonb, 'Properties', 'UserDetails', 'UserDetails', 'StreetNumber', 'Text') AS StreetNumber,
    jsonb_extract_path_text(D.Content::jsonb, 'Properties', 'UserDetails', 'UserDetails', 'City', 'Text') AS City
FROM 
    UserIndex UI
JOIN 
    Document D ON UI.DocumentId = D.Id
JOIN 
    UserByRoleNameIndex_Document UBRNID ON UI.DocumentId = UBRNID.DocumentId
JOIN 
    UserByRoleNameIndex UBRNI ON UBRNID.UserByRoleNameIndexId = UBRNI.Id
GROUP BY 
    UI.DocumentId, 
    UI.UserId, 
    UI.NormalizedUserName, 
    UI.NormalizedEmail,
    D.Content

This is very frustrating 😤😟🫠

Thank you

[MarGraz]

@lampersky in the end, the problem was related to some syntax errors and the cast ::, as explained in the previous messages.

Now this query works because I have implemented a custom function to cast from text to jsonb:

SELECT 
    UI."DocumentId", 
    UI."UserId", 
    UI."NormalizedUserName", 
    UI."NormalizedEmail", 
    STRING_AGG(UBRNI.RoleName, ',') AS RoleNames,
    STRING_AGG(casttotext(UBRNID.UserByRoleNameIndexId), ',') AS RoleIds,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'Name', 'Text') AS FirstName,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'Surname', 'Text') AS LastName,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'Phone', 'Text') AS Phone,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'WhatsApp', 'Text') AS WhatsApp,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'Street', 'Text') AS Street,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'StreetNumber', 'Text') AS StreetNumber,
    jsonb_extract_path_text(cast_text_to_jsonb(D."Content"), 'Properties', 'UserDetails', 'UserDetails', 'City', 'Text') AS City
FROM 
    "UserIndex" UI
JOIN 
    "Document" D ON UI."DocumentId" = D."Id"
JOIN 
    "UserByRoleNameIndex_Document" UBRNID ON UI."DocumentId" = UBRNID."DocumentId"
JOIN 
    "UserByRoleNameIndex" UBRNI ON UBRNID."UserByRoleNameIndexId" = UBRNI."Id"
GROUP BY 
    UI."DocumentId", 
    UI."UserId", 
    UI."NormalizedUserName", 
    UI."NormalizedEmail",
    D."Content"

@sebastienros you might consider adding a cast from text to jsonb as a feature in the generic dialect (see also this issue).

Here is how I implemented the custom function:

CREATE OR REPLACE FUNCTION cast_text_to_jsonb(text_data text)
RETURNS jsonb AS $$
BEGIN
    RETURN text_data::jsonb;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

@lampersky @sebastienros I noticed that if I add more properties to my CustomUserSettings content type, and I include them in the query above, I get an error when I click the Run button in "Search" > "Queries" > "All queries" of the Admin backoffice:

HTTP Error 404.15 - Not Found. The request filtering module is configured to deny a request where the query string is too long

Instead, if I open another shorter query using the Run button, and then paste my long query into the editor, it works.

Should I open a bug report? The problem seems to be related to this.

Thank you

[sebastienros]

Probably the query should be sent as a POST message with the query in the body instead of a get and a query string

[MarGraz]

I opened a new issue here about the 404.15 error.

Thank you