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

MarGraz · 2024-04-16T11:31:07Z

MarGraz
Apr 16, 2024

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

Answered by MarGraz

Apr 16, 2024

@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', '

View full answer

MarGraz · 2024-04-16T11:38:37Z

MarGraz
Apr 16, 2024
Author

@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

2 replies

lampersky Apr 16, 2024

@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 Apr 16, 2024
Author

@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 · 2024-04-16T12:55:59Z

lampersky
Apr 16, 2024

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

5 replies

MarGraz Apr 16, 2024
Author

@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 Apr 16, 2024
Author

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 Apr 16, 2024
Author

@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

Answer selected by MarGraz

sebastienros Apr 16, 2024
Maintainer

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 Apr 17, 2024
Author

I opened a new issue here about the 404.15 error.

Thank you

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Replies: 2 comments 7 replies

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

Select a reply

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

MarGraz Apr 16, 2024

Replies: 2 comments · 7 replies

MarGraz Apr 16, 2024 Author

lampersky Apr 16, 2024

MarGraz Apr 16, 2024 Author

lampersky Apr 16, 2024

MarGraz Apr 16, 2024 Author

MarGraz Apr 16, 2024 Author

MarGraz Apr 16, 2024 Author

sebastienros Apr 16, 2024 Maintainer

MarGraz Apr 17, 2024 Author

MarGraz
Apr 16, 2024

Replies: 2 comments 7 replies

MarGraz
Apr 16, 2024
Author

MarGraz Apr 16, 2024
Author

lampersky
Apr 16, 2024

MarGraz Apr 16, 2024
Author

MarGraz Apr 16, 2024
Author

MarGraz Apr 16, 2024
Author

sebastienros Apr 16, 2024
Maintainer

MarGraz Apr 17, 2024
Author