table provider

table provider_properties

here I wanted the result something like

{
  "provider_uuid": "b897f790-ee90-4869-9345-690380b1a0c9",
  "name": "Provider 1",
  "password": "112233",
  "username": "provider_1",
  "properties": {
    "address_1": "Dubai",
    "address_2": "NY"
  }
}

here i tried something like

SELECT provider.*, json_object(provider_properties.key_name, provider_properties.key_value) as properties FROM provider JOIN provider_properties on provider.provider_id = provider_properties.provider_uuid;

but the problem is i am getting 2 rows

CodePudding user response：

Since your expected results need specific formatting in json. You want to refer to JSON Formatted output. This is for shell though.

You might also want to try this...

This would give you a tabular results but in one row.

SELECT 
    provider.*, 
    p_props.properties 
FROM provider 
JOIN (
    provider_uuid,
    SELECT JSON_OBJECTAGG(
        provider_properties.key_name,
        provider_properties.key_value
    ) as properties
    FROM provider_properties 
    GROUP BY provider_uuid
) p_props ON provider.provider_id = p_props.provider_uuid;

CodePudding user response：

1. CONCAT() and REPLACE() here are used for sorting the order of columns.
2. I would say the use of REPLACE() here is quite risky, please test it out and see how it goes.

db<>fiddle

SELECT 
    p.*,
    CONCAT(
        '{"', 'provider_uuid": "', pp.provider_uuid, '"',
        REPLACE(JSON_OBJECT('name', p.name, 'password', p.password, 
                            'username', p.username, 'properties', pp.name_value), '{"name', ', "name')
    ) AS your_json
FROM provider p
JOIN (
    SELECT provider_uuid, JSON_OBJECTAGG(key_name, key_value) as name_value
    FROM provider_properties 
    GROUP BY provider_uuid
) pp ON p.provider_id = pp.provider_uuid;